DB2 UDB 8.2 Intro

1
DB2 UDB 8.2 Intro

• Helia / Martti Laiho

2
DB2 Family of Editions

• DB2 Everyplace
• Personal Edition
• Satellite Edition
• Workgroup Edition
• Enterprise Edition
• Enterprise-Extended Edition
• DB2 Personal Developer's Edition
• DB2 Universal Developer's Edition

3
DB2 Administration Programs
4
Add-on Products

• DB2 Connect
• DB2 Data Links Manager
• DB2 Query Patroller
• Spatial Extender
• XML Extender
• Replication
• Data Warehouse
• Information Catalog Center

5
Development

• Development Center
• Visual Studio . integrations

6
DB2 Objects

• System
• Instance(s) - environment, security
• Database(s) - config, catalog, logs
• Nodegroup(s)
• Partition(s)
• Tablespace(s)
• Table(s)
• Index(es)
• Long data

7
Table spaces

• An allocation of physical storage for a table
  space is called a container
• A Table space can be
• SMS - System managed space
• files in the operating system file space
• DMS - Database managed space
• container is a fixed size pre-allocated file, or
• raw disk space

8
Instance and Databases
Database manager configuration file
db2systm
Instance
Database
Database data files
Database configuration file
Log files
. ..
Database
Database data files
Database configuration file
Log files
9
DB2 Directories and Files (Windows)
Programs
system database directory
node directory
DB2 Instance information
DAS
10
Samples
11
Database
Applications
Tools
Fast access!
(Threading pool)
Control Caches
Buffer pool
- Connections, open DBs - Locks, open objects -
Procedures
- Pinning - LRU
x
Checkpoint
Log caches
Slow access!
WAL
before image / after image
Database
Commit/Rollback
Data files
Catalog
Configuration file
Recovery log file
Recovery history file
12
Constraints

• NOT NULL
• Unique
• Primary Key
• Foreign key
• Check

13
Data Types

• Numeric data
• Smallint, Integer, BigInt, Decimal, Real, Double
• String data
• Single byte character strings
• Char, Varchar, Long varchar, Clob
• Double byte character strings
• Graphic, Vargraphic, Long Vargraphic, DBClob
• Binary strings
• BLOB
• Date and time data
• Date, Time, Timestamp

14
Controlling Authorities

• SYSADM - System Administration Authority
• member in NT Administrators
• SYSCTRL - System Control Authority
• SYSMAINT - System Maintenance Authority
• DBADM - Database Administration Authority
• creator and administrator of a single database
• User authentication based on OpSys authentication

15
Installing the Server

• Communication software
• TCP/IP preferred
• User account of the installer
• belong to Administrators group
• name max 8 chars
• Advanced NT user rights
• Act as part of the operating system
• Increase quotas
• Create a token object
• Replace a process level token

16
Installing a DB2 Client

• Administration client
• Control Center
• Command Center, Performance Monitor, ...
• Run-time clients
• Client Configuration Assistant (CCA)
• Command Center

17
Command Line Processor
18
Control Center
19
Instance Administration Tasks
20
Control Center
21
Database Administration Tasks
22
Backup Wizard ..
23
.. Backup Wizard
24
.. Backup Options
25
.. Backup Performance
26
.. Backup Scheduling
27
Backup Wizard Summary
28
Backing Up a Database
29
Offline Backup Ready
30
Backed Up
31
Customized Control Center
32
Change User (Security)
33
Event Monitor ..
34
.. Event Monitor
35
.. Event Monitor
36
.. Event Monitor
37
Tools ..
38
Health Center
39
(No Transcript)
40
Information Catalog Center
41
Command Center
42
Access Plan
43
Development Center
44
(No Transcript)
45
(No Transcript)
46
(No Transcript)
47
(No Transcript)
48
(No Transcript)
49
Stored Procedure Builder
50
.. Stored Procedure Builder
51
.. Stored Procedure SQL Statement
52
.. A Generated Stored Procedure
CREATE PROCEDURE MARTTI.Proc1 ( OUT var0 int )
SPECIFIC MARTTI.Test1 RESULT SETS 1
LANGUAGE SQL -------------------------------------
----------------------------------- -- SQL Stored
Procedure ---------------------------------------
--------------------------------- P1 BEGIN
-- Declare variable DECLARE var0_TMP int
DEFAULT 0 -- Declare cursor DECLARE
cursor1 CURSOR WITH RETURN FOR SELECT
COUNT() FROM Db2Admin.Employee -- Cursor
left open for client application OPEN
cursor1 SET var0 var0_TMP END P1
53
CLI
54
Journal Task History
55
Journal Database History
56
Journal Database History
57
Journal - Messages
58
Journal Notification Log
59
Memory Visualizer
60
Information Center