DB2 Design and Development

Course

In Edinburgh

£ 1,500 + VAT

Description

  • Type

    Course

  • Location

    Edinburgh (Scotland)

  • Duration

    5 Days

The aim of this Course is to provide the programmer, unfamiliar with DB2, with the necessary skills required to define, access and manipulate DB2 data, either via an application program or using SPUFI. On completion of this Course the student will be able to: use design techniques such as Normalisation,understand the performance issues involved in constructing a system,implement an efficient. Suitable for: This course provides the applications programmer with an in-depth knowledge of the DB2 development process together with an understanding on the DB2 design process. It is aimed at programmers who need to design, code or support DB2 application programs written in COBOL, PL1 or C.

Facilities

Location

Start date

Edinburgh (Midlothian/Edinburghshire)
16 St. Mary'S Street, EH1 1SU

Start date

On request

About this course

The delegate should be familiar with the z/OS host environment, together with a working knowledge of either COBOL, PL/I or C program development using TSO / ISPF.

Questions & Answers

Add your question

Our advisors and other users will be able to reply to you

Who would you like to address this question to?

Fill in your details to get a reply

We will only publish your name and question

Reviews

Course programme

Course Objectives
The aim of this Course is to provide the programmer, unfamiliar with DB2, with the necessary skills required to define, access and manipulate DB2 data, either via an application program or using SPUFI. On completion of this Course the student will be able to:

  • use design techniques such as Normalisation
  • understand the performance issues involved in constructing a system
  • implement an efficient tablespace, table and index design
  • set up a DB2 test environment, using correctly defined tables, views, indexes, synonyms and aliases
  • use both permanent and temporary tables
  • use the DB2I toolset, including SPUFI
  • code SQL statements to read and manipulate DB2 data
  • develop, prepare and execute DB2 programs
  • bind packages and plans
  • use non-scrollable and scrollable cursors
  • describe the locking process used by DB2
  • create Triggers
  • define and use Referential Integrity
  • define and use Table Check Constraints
  • describe the security features of DB2
  • produce EXPLAIN output using a PLAN_TABLE
  • use the LOAD utility to populate tables

Course Details
INTRODUCTION TO DB2
The Relational Model
Data Representation
The DB2 Environment
DB2 Table Structure
DB2 Data Types
DB2 Catalog
DB2 Interactive
SPUFI
Accessing The Data
Interfaces to DB2
SQL Structure
Embedded SQL
DB2 Data Relationships
Access Path Selection
Database Design / Data Analysis
Overview of Normalisation
First Normal Form
Order Form Example
Second Normal Form
Third Normal Form
Check 3NF
THE DESIGN PROCESS
Introduction - The Basic Elements of a Database
The Flat File Database
The Hierarchical Database
The Relational Database
Relational Database Advantages
Database Design Phases
Key Roles in the Design Process
CONCEPTUAL DESIGN
Why Analyse?
Who Will be Involved in the Analysis?
Entities
Entity Relationships - A Worked Example
Entity Relationship Diagrams
Relationship Types
Many to Many Relationships - Association Entities
Common Parentage
Attributes
LOGICAL DATABASE DESIGN
Overview of Normalisation
Benefits of Normalisation
Primary and Foreign Keys
Primary Key Considerations
The Normalisation Process
First Normal Form
Second Normal Form
Third Normal Form
Fourth Normal Form
Fifth Normal Form
Normalisation Check - Homonyms and Synonyms
Conversion of Entities to Base Tables
Attributes
Identification of Keys
PHYSICAL DATABASE DESIGN
Introduction
Gathering of Required Information
Data Flow Diagrams
Access Path Requirements
Entity Life Histories
Data And Relationship Volumes
Denormalisation Considerations
Denormalisation Techniques
RI Requirements
Index Requirements
Locking Requirements
Locking Considerations
View Requirements
DATA DEFINITION LANGUAGE
The Structure of DB2 Objects
Definition of DB2 Objects - DDL
Database Definition
Tablespace / Page Organisation
Page Sizes
Tablespaces
Tablespace Layout
Types of Tablespace
Tablespace Definition
Tables
Table Definition
Table Names
Copying Table Definitions
Rename Table
Column Types
Null Values
User Defined Default Values
Identity Columns
Identity Columns - Examples
Identity Columns - Considerations
Identity Columns - Restrictions
Identity_Val_Local Function
Temporary Tables
Global Temporary Tables
Declared Temporary Tables
Declared Temporary Table Considerations
Declared Temporary Tables Comparisons
Views
Read Only Views
Views - With Check Option
Views Based Upon Other Views
Creating a View of Two Tables
Synonyms and Aliases
Indexes
Index Organisation - The B Tree Index
Index Clustering
Non-Unique Indexes
Index Definition
Partitioning Indexes
Index Design Considerations
The Alter Statements
The Drop Statements
DB2 INTERACTIVE (DB2I)
DB2I Options
SPUFI - SQL Processor Using File Input
Running Queries
SPUFI Defaults
SPUFI - Setting AUTOCOMMIT to NO
DB2 Commands
DB2 Utilities
The DB2I Defaults Panel
DATA MANIPULATION LANGUAGE
SQL - Structured Query Language
DB2 Environments
SQL Features
SQL Query Results
The SELECT Statement
The ''As'' Clause
Column concatenation
Expressions
Functions
Special Registers
The WHERE Clause
Special Operators
NOT Operand
IN Operand
LIKE Operand
BETWEEN Operand
Statements Using Nulls
Column Functions
Using ''Distinct''
The GROUP BY Clause
The HAVING Clause
The ORDER BY Clause
Fetch First ''n'' Rows Only Clause
Special Registers
Current Date
Current Time
Current Timestamp
User Keyword
The UPDATE Statement
Update with Subselect
The DELETE Statement
The INSERT Statement
The Mass Insert Statement
Functions
Scalar Functions
Function Examples
Date, Time and Timestamp Functions
The Case Statement
JOINS
Cartesian Joins
Inner Joins
Outer Joins
Nested Table Expression
The UNION Statement
Subqueries
Subqueries Using in
Exists
The ''All'' Subquery
The ''Any'' Or ''Some'' Subquery
APPLICATION PROGRAMMING
DB2 Environments
Development Cycle With DB2
Precompilation
DBRM''s
DCLGENs
Plans
Packages
SQL Statement Format - COBOL
SQL Statement Format - PL/1
SQL Statement Format - C
Table Declaration
SQL Statements Used in Application Programs
SQL INCLUDE
SQL Communication Area
Decoding The Sqlca in a Program
Retrieving Data Into Host Variables
Ambiguous Host Variables
SQL Error Codes
Singleton Selects
Cursors
Using a Cursor To Retrieve a Result Set
Declare Cursor
Open Cursor
Fetch a Row
Row Update
Row Deletion
Close Cursor
With Hold Option
The Optimize Statement
Fetch First ''n'' Rows Only Clause
Fetch First vs Optimize for
Handling Nulls
Retrieving System Registers
ROWID - Direct Row Access
Direct Row Access - Example
Table Names in Application Programs
Batch Execution
SCROLLABLE CURSORS
Scrollable Cursors
Declaring a Scrollable Cursor
Scrollable vs. Non-scrollable Cursors
Updatable Cursors
Fetching from a Scrollable Cursor
Absolute Fetching Examples
Relative Fetching Examples
Insensitive and Sensitive Cursors
Fetch Sensitivity for Sensitive Cursors
Sensitive Fetches - Update and Delete Holes
Sensitive Fetches - Updated Data
Scrollable Cursor - Locks on the Base Table
PLANS AND PACKAGES
Bind
Rebind
Plans and Packages
Advantages of Using Packages
Binding Packages
Binding Plans
Defaults for Binding
Binding in Batch
Identifying a Collection Within a Program
Program Execution in Batch
Plan Names in Application Programs
LOCKING
Implications of Concurrent Processing
IMS Resource Lock Manager (IRLM)
DB2 Locking Methods
The Lock Table Statement
Lock Modes
When Locks are Acquired
When Locks are Released
Isolation Levels
Controlling The Isolation Level At SQL Level
Keep Update Locks
Unit of Work in TSO
Commit and Rollback
Declaring Cursors With Hold
CICS Issues
IMS Issues
Savepoints
Savepoint Definition
Savepoints - Considerations and Restrictions
Transaction Deadlocks
Design Considerations
SECURITY
Using DB2 Views
Controlling Resource Access - Data Control Language
DB2 Special Users
Database Privileges
Table Privileges
Authorisation of Plans
Plan / Package Privileges
Retain Execution Authority
Primary, Secondary and Current Authids
Grant Examples
Revoke Examples
Grant and Revoke Notes
SYSTEM CATALOG
Catalog Contents
Catalog Tables
Catalog Access
TRIGGERS
Triggers
Trigger Parts
Before and After Triggers
Trigger Examples
Error Handling
Trigger Cascading
Ordering of Multiple Triggers
Trigger Authorisation
Catalog Information for Triggers
Removing Triggers
REFERENTIAL INTEGRITY
What Is Referential Integrity?
Parent and Dependent Tables
The Primary Key
The Foreign Key
Referential Constraint Rules
Constraint Names
More Complex Referential Structures
Check Pending Status
Resetting Check Pending Status
Referential Integrity Access
Design Considerations
TABLE CHECK CONSTRAINTS
Overview of Check Constraints
Constraint Syntax
Allowable Constraints
When are Constraints Enforced?
Current Rules
When Is Check Pending Set
PERFORMANCE AND TUNING
The DB2 EXPLAIN Function
SQL Explain Statement
Plan Table Layout
Plan Table Column Definitions
Predicate Evaluation
Indexable Predicates
Stage 1 and Stage 2 Predicates
Predicate Evaluation Sequence
UTILITIES
Introduction
Utility Overview
Running Utilities
The LOAD Utility
Online Load Resume
Online Load Considerations
Course Environment Development will be performed using:

  • IBM Mainframe

Course Format: The course contains many practical exercises to ensure familiarity with the product. Initially students create Tables and Indexes, and then proceed to develop COBOL, PL/I or C programs accessing the data held on the DB2 Tables. On completion of this course students will be ready to start the development of application programs accessing DB2 data. The comprehensive Student Guide supplied is fully indexed serving as a useful reference tool long after the course has finished. Delegates will also be able to access a free help-line with technical questions relating to topics covered on the course. An IBM SQL REFERENCE SUMMARY HANDBOOK is provided free to all attendees as part of the training course.

DB2 Design and Development

£ 1,500 + VAT