DB2 Family Fundamentals

basesprocketΔιαχείριση Δεδομένων

31 Οκτ 2013 (πριν από 4 χρόνια και 9 μέρες)

97 εμφανίσεις

DB2FamilyFundamentals
(Course Code CF03)
Instructor Guide
ERC4.0
IBM Learning Services
Worldwide Certified Material
Instructor Guide
Publishing Information
This publication has been produced using BookMaster (Program Number
5668-015),the Document Composition Facility (Program Number 5748-XX9),and
Freelance 97 for Windows.It was printed on the IBM 3820 Page Printer.
Trademarks
The following are trademarks International Business Machines Corporation in the
United States,or other countries,or both:
Approach,Freelance Graphics,Lotus and Lotus Notes are trademarks of Lotus
Development Corporation in the United States,or other countries,or both.
Java and all Java-based trademarks are trademarks of Sun Microsystems,Inc.in the
United States,other countries,or both.
Microsoft,Windows,Windows NT,and the Windows logo are trademarks of Microsoft
Corporation in the United States,other countries,or both.
Intel is a registered trademark of Intel Corporation in the United States,other
countries,or both.
UNIX is a registered trademark in the United States and other countries licensed
exclusively through The Open Group.
Linux is a trademark of Linus Torvalds.
Other company,product,and service names may be trademarks or service marks of
others.
AIX
BookMaster
CICS
DataGuide
DataJoiner
DataPropagator
DYNIX
DB2
DB2 Universal Database
Distributed Relational
Database Architecture
DRDA
IMS
Intelligent Miner
MVS
Net.Data
NUMA-Q
OS/2
OS/390
PTX
QMF
RACF
Sequent
Visual Warehouse
VisualAge
VisualGen
December,2000
The information contained in this document has not been submitted to any formal IBM test and is
distributed on an Èas i s¼ basis without any warranty either express or implied.The use of this
information or the implementation of any of these techniques is a customer responsibility and depends
on the customer's ability to evaluate and integrate them into the customer's operational environment.
While each item may have been reviewed by IBM for accuracy in a specific situation,there is no
guarantee that the same or similar results will result elsewhere.Customers attempting to adapt these
techniques to their own environments do so at their own risk.The original repository material for this
course has been certified as being Year 2000 compliant.
© Copyright International Business Machines Corporation 1997,2000.All rights reserved.
This document may not be reproduced in whole or in part without the prior written permission of IBM.
Note to U.S.Government Users Í Documentation related to restricted rights Í Use,duplication or
disclosure is subject to restrictions set forth in GSA ADP Schedule Contract with IBM Corp.
Instructor Guide
Contents
Instructor Course Overview
........................................
xi
Course Description
.............................................
xiii
Agenda
......................................................
xv
Units
Unit 1.Understanding A Table
....................................
1-1
Unit Objectives
................................................
1-2
1.1 Understanding A Table
.......................................
1-5
The ÌEase¹ of Working With a Relational Database
..................
1-6
Find Phone Number of Larry Jones From Sales
...................
1-8
The Problem With Hard Copy Data
............................
1-10
Load Data in a DBMS
.....................................
1-12
Some of the Integrated Services DB2 UDB Provides
...............
1-14
Concurrency - Down to the Row Level
.........................
1-16
Integrity
...............................................
1-18
Security
...............................................
1-20
DB2 UDB Is a Relational Database Management System
............
1-22
Table Creation - English
...................................
1-24
The Language of Relational Database Management Systems
.........
1-26
Creating Tables in DB2 UDB - We Must Provide...
.................
1-28
Table and Column Naming Conventions
........................
1-30
Common Column Data Types
................................
1-32
NULL Characteristic
......................................
1-34
From an Editor that Talks to DB2 UDB
.........................
1-36
Unique Key,Primary Key
...................................
1-38
CREATE TABLE Statement
..................................
1-40
Foreign Key
............................................
1-42
Example CREATE TABLE Statement
...........................
1-44
Primary Keys,Foreign Key
.................................
1-46
A Look at Three Related Tables
..............................
1-48
A ÌBIG¹ Picture Look at Running an Application
..................
1-50
How DB2 UDB Handles an SQL"Change"Request
................
1-52
Finishing the Change Request
...............................
1-54
Potential SYSADM Tasks
...................................
1-56
Potential DBADM Tasks
....................................
1-58
Potential Programmer Tasks
................................
1-60
Potential End-User Tasks
...................................
1-62
Potential Operator Tasks
...................................
1-64
Checkpoint
..................................................
1-67
Unit Summary
................................................
1-70
Unit 2.How Does An End-User Use DB2 UDB?
.........................
2-1
Unit Objectives
................................................
2-2
2.1 Writing SQL
...............................................
2-5
Who Is an End-User?
.......................................
2-6
Contents iii
©
Copyright IBM Corp.1997,2000
Course materials may not be reproduced in whole or in part
without the prior written permission of IBM.
Instructor Guide
Running Programs that ÌTalk¹ to DB2 UDB
.......................
2-8
And If I'm Writing My Own SQL?
.............................
2-10
STAFF Table Description
...................................
2-12
Example TABLE - STAFF
...................................
2-14
ORG Table Description
....................................
2-18
Example TABLE - ORG
....................................
2-20
Four SELECT Statement Clauses
.............................
2-24
To Retrieve All Columns for All Rows
..........................
2-28
To SELECT Specified Rows
.................................
2-30
Base Table
.............................................
2-32
Conceptual Intermediate Table
...............................
2-34
Numeric Comparisons
.....................................
2-36
SQL Comparison Alternatives
...............................
2-38
Functions - Two Types
.....................................
2-40
Column Functions
........................................
2-42
Scalar Functions
.........................................
2-44
UPPER
................................................
2-46
Example SELECT Statement
.................................
2-48
Conceptual Execution of a SELECT
............................
2-50
Conceptual Execution of a SELECT (Cont)
.......................
2-52
SELECT Clause - Column Naming
............................
2-54
JOIN - Retrieving Data from Multiple Tables
.....................
2-56
Example Tables
..........................................
2-58
JOIN - Traditional Syntax
...................................
2-60
INNER JOIN - Alternate Syntax
...............................
2-62
OUTER JOIN (INNER JOIN + )
...............................
2-64
RIGHT OUTER JOIN
.......................................
2-66
LEFT OUTER JOIN
........................................
2-68
CASE Expressions
........................................
2-70
Inserting Rows into the Table
................................
2-72
Update Columns
.........................................
2-74
Delete Rows
............................................
2-76
OLAP/Multi-Dimensional Analysis
...........................
2-78
Statistical Functions
......................................
2-80
Rollup
................................................
2-82
Ranking Functions - Detail
..................................
2-84
Ranking Functions - Summarizing
............................
2-86
Ranking Within Sets
......................................
2-88
Reporting Functions
......................................
2-90
Cumulative Functions
.....................................
2-92
Percent Change
.........................................
2-94
Curve Smoothing
........................................
2-96
Data Mining
............................................
2-98
Intelligent Miner - Algorithms
................................
2-100
2.2 Ways to Run SQL
..........................................
2-103
How Do I ÌTalk¹ to DB2 UDB?
................................
2-104
Command Line Processor - Interactive
.........................
2-106
Command Center
........................................
2-108
QMF for Windows
........................................
2-110
Returning Data
..........................................
2-112
Saving the Data
..........................................
2-114
Save Data to New Database
.................................
2-116
iv DB2 Family Fundamentals
©
Copyright IBM Corp.1997,2000
Course materials may not be reproduced in whole or in part
without the prior written permission of IBM.
Instructor Guide
Format the Report
........................................
2-118
Save Data into a Spreadsheet
...............................
2-120
Data in Spreadsheet
......................................
2-122
OS/390 SPUFI
...........................................
2-124
Compose Query
.........................................
2-126
Execute the Query
........................................
2-128
Output
................................................
2-130
QMF for OS/390
.........................................
2-132
Compose Query in QMF
...................................
2-134
QMF Tabular Report
......................................
2-136
QMF Tailored Report
......................................
2-138
QMF Report Formatting Instructions
...........................
2-140
QMF Procedures
.........................................
2-142
ODBC Access to DB2 UDB
..................................
2-144
Specify the Table
.........................................
2-146
Checkpoint
..................................................
2-149
Unit Summary
................................................
2-154
Unit 3.How Does A Programmer Use DB2 UDB?
.......................
3-1
Unit Objectives
................................................
3-2
3.1 Creating A Test Environment
...................................
3-5
Creating A Test Environment - Tables
...........................
3-6
Creating A Test Environment - Indexes and Views
..................
3-8
Creating A Test Environment - Catalog Statistics
..................
3-10
Print A ÌSnapshot¹ of the Test Tables
..........................
3-12
3.2 Prototyping SQL
...........................................
3-15
Pencil Check the Logic - So You Know The Answer,First!
...........
3-16
Prototyping ÌChange¹ SQL Using A Unit of Work
..................
3-18
Have You Ever Thought...
...................................
3-20
Prototype SQL Against Test Tables
............................
3-22
EXPLAIN the Queries
......................................
3-24
Visual Explain
...........................................
3-26
3.3"Traditional"Programming Role
...............................
3-29
A Program With SQL
......................................
3-30
Steps to Coding SQL in a Program
............................
3-32
Handling Exception Conditions
...............................
3-34
The SQL Delimiters
.......................................
3-36
Precompile
.............................................
3-38
Compile and Link Edit
.....................................
3-42
Bind Package
...........................................
3-44
Bind Package (Cont)
......................................
3-46
Bind Package (Cont)
......................................
3-48
Bind Plan - OS/390
.......................................
3-50
Program Execution - OS/390
................................
3-52
Program Execution - UNIX/Intel
..............................
3-54
Program Preparation Summary
..............................
3-56
3.4 Stored Procedures
.........................................
3-59
Why Use Stored Procedures?".
..............................
3-60
Applications with Stored Procedures
..........................
3-62
Calling A Stored Procedure From A Program
....................
3-64
Calling A Stored Procedure
.................................
3-66
Stored Procedure Builder
..................................
3-68
Contents v
©
Copyright IBM Corp.1997,2000
Course materials may not be reproduced in whole or in part
without the prior written permission of IBM.
Instructor Guide
SQL Assistant
...........................................
3-70
Testing the Stored Procedure
................................
3-72
Stored Procedures - Summary
...............................
3-74
3.5 Host Variables
............................................
3-77
Host Variables
..........................................
3-78
In COBOL,C
............................................
3-80
3.6 A SELECT in a Host Program
.................................
3-83
Selecting A Single Row
....................................
3-84
Selecting Multiple Rows
...................................
3-86
Fetch
.................................................
3-88
Handle Exception Conditions
................................
3-90
Fetch
.................................................
3-92
Scrollable Cursors
.......................................
3-94
Block Fetch in a Distributed Environment
.......................
3-96
3.7 Static versus Dynamic SQL
...................................
3-99
Static SQL - An SQL Statement that DB2 UDB Has!
................
3-100
Dynamic SQL - Statements DB2 UDB Doesn't Have
................
3-102
Static versus Dynamic
.....................................
3-104
Call Level Interface (CLI)
...................................
3-106
3.8 Application Generators
......................................
3-109
Application Generators
....................................
3-110
Application Generators Key Functions
.........................
3-112
Java
..................................................
3-114
Net.Data
...............................................
3-116
Programmer - The Center of Competency
.......................
3-118
Checkpoint
..................................................
3-121
Unit Summary
................................................
3-126
Unit 4.What Does An Administrator Do in DB2 UDB?
....................
4-1
Unit Objectives
................................................
4-2
4.1 What Does A System Administrator Do in DB2 UDB?
.................
4-5
What Does A System Administrator Do in DB2 UDB?
...............
4-6
IBM DB2 UDB Family
......................................
4-8
DB2 UDB Runs in a Massively Parallel Processor Environment
.......
4-12
Why Massively Parallel Processors For VLDBs
...................
4-14
How Can We Fill the Pool Faster?
............................
4-16
What Happens When We Have to Clean the Pool?
.................
4-18
Possible Options - How about 209 Pools?
.......................
4-20
Installation - Physical Install
................................
4-22
Create System Level Objects
................................
4-24
Using the DB2 UDB Control Center
...........................
4-26
OS/390
................................................
4-28
UNIX/Intel
..............................................
4-30
Grant Privileges
.........................................
4-32
Monitor DB2 UDB's Performance
.............................
4-34
DB2 UDB Estimator - Sizings
................................
4-36
Install,Build Data Warehouse
...............................
4-38
Data Warehouse Center
....................................
4-42
4.2 What Does A Database Administrator Do in DB2 UDB?
...............
4-45
What Does A Database Administrator Do in DB2 UDB?
.............
4-46
How Does A DBA Know What To Create?
.......................
4-48
Example CREATE TABLE Statement
...........................
4-50
vi DB2 Family Fundamentals
©
Copyright IBM Corp.1997,2000
Course materials may not be reproduced in whole or in part
without the prior written permission of IBM.
Instructor Guide
Setting Specific Column Default Values
.........................
4-52
Adding Business Rules to a Table's Definition - At CREATE TABLE Time 4-54
Adding Business Rules to a Table's Definition - With ALTER TABLE
....
4-56
Creating Tables Using the Table Wizard
........................
4-58
Name the Table
..........................................
4-60
Choose Columns
.........................................
4-62
Change Column Definitions
.................................
4-64
Identity Characteristic
.....................................
4-66
Defining a Primary Key
....................................
4-68
Specify the Table Space
....................................
4-70
Review SQL - Finish
......................................
4-72
Alter the Table
..........................................
4-74
Adding Business Rules,Foreign Keys,and so on
.................
4-76
4.3 Indexes
.................................................
4-79
Tables Without Indexes - From the Table FRANCE.PARIS
...........
4-80
An Index Is:
............................................
4-82
What Is An Index?
........................................
4-84
Unique Index
...........................................
4-86
Index Structure
..........................................
4-88
Non-Clustered Index
......................................
4-90
Clustered Index
.........................................
4-92
Clustered and Non-Clustered Index Access
.....................
4-94
Creating Indexes
.........................................
4-96
Creating an Index
........................................
4-98
Determining What Indices to Create
...........................
4-100
4.4 Views
...................................................
4-103
Views
.................................................
4-104
Creating A View
.........................................
4-106
Using A View
...........................................
4-108
Updating Through A View
..................................
4-110
4.5 DROP
..................................................
4-113
The DBA Cleans Up
.......................................
4-114
Clean Up Using the GUI
....................................
4-116
DROP Dependencies
......................................
4-118
4.6 Access to Remote Data Sources
...............................
4-121
Universal Access to a Universe of Data
........................
4-122
Cataloging Databases
.....................................
4-124
4.7 Locking
.................................................
4-127
Locking - Concurrency Needs - OS/390
.........................
4-128
Concurrency - Table Level (1 of 6)
............................
4-130
Concurrency - Table Level (2 of 6)
............................
4-132
Concurrency - Table Level (3 of 6)
............................
4-134
Concurrency - Table Level (4 of 6)
............................
4-136
Concurrency - Table Level (5 of 6)
............................
4-138
Concurrency - Table Level (5 of 6)
............................
4-140
Concurrency - Page Level - OS/390
...........................
4-142
Concurrency - Row Level
...................................
4-144
OS/390 Specifying LOCKSIZE (Concurrency Level)
................
4-146
Isolation Level
..........................................
4-148
4.8 Utilities
.................................................
4-151
Utilities - DB2 UDB Tools
...................................
4-152
Setup and Move Utilities
...................................
4-154
Contents vii
©
Copyright IBM Corp.1997,2000
Course materials may not be reproduced in whole or in part
without the prior written permission of IBM.
Instructor Guide
Recovery Related Utilities
..................................
4-156
Data Recovery - Good Data
.................................
4-158
Speeding Up Recovery - OS/390
.............................
4-160
Point-In-Time Recovery - OS/390
.............................
4-162
Version Recovery - UNIX/Intel (Restore)
........................
4-164
Maintenance Utilities
......................................
4-166
Expanded Rows
.........................................
4-168
REORG - Clustering the Data on Department
....................
4-170
RUNSTATS
.............................................
4-172
4.9 Other DBA Tasks
..........................................
4-175
Other DBA Tasks
.........................................
4-176
User-Defined Distinct Types
.................................
4-178
Creating a User-Defined Distinct Type (UDT)
.....................
4-180
Using the UDT
...........................................
4-182
Creating a User-Defined Function
.............................
4-184
4.10 Security
................................................
4-187
To Get to DB2 UDB
.......................................
4-188
DB2 UDB Security
........................................
4-190
Everything Is Protected!
....................................
4-192
Privileges
..............................................
4-194
Owner's (Implicit) Table Privileges
............................
4-196
Explicit Table Privileges
....................................
4-198
Indirect Privileges - Static SQL
...............................
4-200
Indirect Privileges - Group
..................................
4-202
Administrative Authorities
..................................
4-204
Views and Security
.......................................
4-206
Revoke
................................................
4-208
Checkpoint
..................................................
4-211
Unit Summary
................................................
4-216
Unit 5.Data Modeling and Database Design
..........................
5-1
Unit Objectives
................................................
5-2
5.1 Business Modeling
..........................................
5-5
Business Modeling
........................................
5-6
Define the Business Entities
..................................
5-8
Draw An Entity Relationship Diagram (ERD)
.....................
5-10
Benefits of Business Modeling
...............................
5-12
5.2 Data Modeling
............................................
5-15
Data Modeling
..........................................
5-16
Identify Primary Keys
.....................................
5-18
Data Modeling
..........................................
5-20
One Table Per Entity
......................................
5-22
One Column for Each Data Element
...........................
5-24
5.3 Table and Column Names
....................................
5-27
Names - Review
.........................................
5-28
Name Examples
.........................................
5-30
Table Name Construction
...................................
5-32
Table Names - References
..................................
5-34
Column Names - Construction
...............................
5-36
5.4 Data Types
...............................................
5-39
Data Types
.............................................
5-40
Numeric Formats
.........................................
5-42
viii DB2 Family Fundamentals
©
Copyright IBM Corp.1997,2000
Course materials may not be reproduced in whole or in part
without the prior written permission of IBM.
Instructor Guide
Character String Formats
...................................
5-44
Variable Length Strings
....................................
5-46
Graphic String Formats
....................................
5-48
Large Objects
...........................................
5-50
Create Table
............................................
5-52
DB2 UDB Relational Extenders
..............................
5-54
User-Defined Distinct Types and User-Defined Functions
............
5-56
Date/Time Formats
.......................................
5-58
5.5 NULLs
..................................................
5-61
Nulls
.................................................
5-62
5.6 Related Data
.............................................
5-65
Related Data
............................................
5-66
Related Tables
..........................................
5-68
Which Table Gets the Foreign Key?
...........................
5-70
5.7 Association Table
..........................................
5-73
Association Table - Many-To-Many
............................
5-74
Association Table - Normal DB2 UDB Table
.....................
5-76
5.8 Referential Integrity
........................................
5-79
The Rules DB2 Enforces to Maintain Referential Integrity
...........
5-80
Adding Primary and Foreign Keys to an Existing Table
.............
5-82
Terms
.................................................
5-84
Processing Rules Enforced to Maintain Referential Integrity
.........
5-86
Primary Key Violation
.....................................
5-88
Foreign Key Violation
.....................................
5-90
Delete Rules
............................................
5-92
Delete Rules - ON DELETE CASCADE
..........................
5-94
Delete Rules - ON DELETE SET NULL
..........................
5-96
Delete Rules - ON DELETE RESTRICT
..........................
5-98
5.9 Triggers
.................................................
5-101
Triggers
...............................................
5-102
Triggers:A Simple Example
.................................
5-104
Triggers:A Sophisticated Example
............................
5-106
5.10 Normalization
............................................
5-109
Normalization - First Normal Form
............................
5-110
Normalization - Second Normal Form
..........................
5-112
Normalization - Third Normal Form
...........................
5-114
Normalization Recommendation
..............................
5-116
Questions to Answer
......................................
5-118
Normalization/Denormalization Example
........................
5-120
Denormalization Example
..................................
5-122
Costs and Benefits of Denormalization
.........................
5-124
Checkpoint
..................................................
5-127
Unit Summary
................................................
5-132
Unit 6.Distributed Data Processing Terminology
.......................
6-1
Unit Objectives
................................................
6-2
6.1 Distributed Data
............................................
6-5
Planning for Distributed Data
.................................
6-6
Planning
................................................
6-8
Ensure Security of the Distributed Data
........................
6-10
Planning - Multiple Skills Requirements
........................
6-12
Data Segmentation
.......................................
6-14
Contents ix
©
Copyright IBM Corp.1997,2000
Course materials may not be reproduced in whole or in part
without the prior written permission of IBM.
Instructor Guide
Planning - Data Synchronization and Integrity
....................
6-16
Planning - Management Approach
............................
6-18
Data Backup and Recovery
.................................
6-20
Distributed Terminology - Unit of Work
.........................
6-22
Types of Access to Distributed Data
...........................
6-24
Checkpoint
..................................................
6-27
Unit Summary
................................................
6-30
Appendix A.Checkpoint Solutions
.................................
A-1
x DB2 Family Fundamentals
©
Copyright IBM Corp.1997,2000
Course materials may not be reproduced in whole or in part
without the prior written permission of IBM.
Instructor Guide
Instructor Course Overview
This course will familiarize the student with the features,functions,
and services provided by DB2.It also gives the student an
introduction to DB2 data processing and the terminology to
provide the student with a foundation for follow on DB2 education.
Instructor Course Overview xi
©
Copyright IBM Corp.1997,2000
Course materials may not be reproduced in whole or in part
without the prior written permission of IBM.
Instructor Guide
xii DB2 Family Fundamentals
©
Copyright IBM Corp.1997,2000
Course materials may not be reproduced in whole or in part
without the prior written permission of IBM.
Instructor Guide
Course Description
DB2 Family Fundamentals
Duration:2 days
Purpose
This course provides you with information about the functions of
IBM's DB2® Universal Database (DB2 UDB),a relational database
manager which may be installed under a variety of operating
systems on many hardware platforms.
DB2 UDB runs under the MVS,VM,OS/2® and Windows NT,
UNIX,and LINUX,operating systems to name a few.
The course includes discussion of how the DB2 UDB products
provide services.The focus is on the services DB2 provides and
how we work with DB2 UDB,not on its internal workings.
Audience
Persons needing an introductory knowledge of DB2,and persons
preparing for advanced and specialized DB2 education.
Objectives
After completing this course,you should be able to:
List and describe the major components of IBM's relational
database DB2 UDB.
Explain the characteristics of a DB2 table
Identify the characteristics of various DB2 column data types
Comprehend the processing instructions given to DB2 via
simple SQL statements
List and describe several ways to build (write) and execute
SQL statements
List and describe steps needed to imbed SQL statements in an
application program
Explain some of the functions performed by,and
responsibilities of,Database and System Administrators
Course Description xiii
©
Copyright IBM Corp.1997,2000
Course materials may not be reproduced in whole or in part
without the prior written permission of IBM.
Instructor Guide
Understand the basic concepts of data modeling
Describe the concepts involved with distributed data and some
implementation considerations
Describe data warehousing and some of the products involved
Establish a base for more specialized DB2 education
Contents
Understanding a Table
How Does An End-User Use DB2 UDB?
How Does A Programmer Use DB2 UDB?
How Does An Administrator Use DB2 UDB?
Data Modeling and Database Design
Distributed Data Processing Terminology
xiv DB2 Family Fundamentals
©
Copyright IBM Corp.1997,2000
Course materials may not be reproduced in whole or in part
without the prior written permission of IBM.
Instructor Guide
Agenda
Day 1
(00:15) Welcome
(02:00) Understanding A Table
(02:00) How Does An End-User Use DB2 UDB?
(01:45) How Does A Programmer Use DB2 UDB?
Day 2
(03:00) What Does An Administrator Do in DB2 UDB?
(02:00) Data Modeling and Database Design
(00:45) Distributed Data Processing Terminology
Agenda xv
©
Copyright IBM Corp.1997,2000
Course materials may not be reproduced in whole or in part
without the prior written permission of IBM.
Instructor Guide
xvi DB2 Family Fundamentals
©
Copyright IBM Corp.1997,2000
Course materials may not be reproduced in whole or in part
without the prior written permission of IBM.