IBM Software Group
DB2 UDB the Basics
Keith E. Gardenhire keithgar@[Link]
2005 IBM Corporation
IBM Software Group
Introduction
DB2 UDB V8.2 Basic Database Administration
2005 IBM Corporation
IBM Software Group
Products
DB2 Enterprise Server Edition DB2 Workgroup Server Unlimited Edition DB2 Workgroup Server Edition DB2 UDB Express Edition DB2 Personal Edition
3
2005 IBM Corporation
IBM Software Group
Data Partitioning Feature
Ethernet
Node 1
Node 2
Node 3
Node 4
Data
Data
Data
Data
2005 IBM Corporation
IBM Software Group
Instance
Manages one or more databases Must have root or Administrator Authority to create
db2icrt is used to create an instance
db2idrop is used to drop an instance db2iupdt is used to update the instance
db2icrt -u fencedusr instance_name db2start db2stop will start an instance will stop an instance
2005 IBM Corporation
IBM Software Group
Creating the Database
Create db database-name
Creates three table spaces Creates System Catalog Tables
2005 IBM Corporation
IBM Software Group
Create Database Command
CREATE DATABASE dbname AS alias_name
2005 IBM Corporation
IBM Software Group
Table Space
Logical layer between Hardware and Database Comprised of one or more containers A container is a file or a directory REGULAR
CREATE
LARGE SYSTEM
TABLESPACE name
TEMPORARY
USER MANAGED BY
8
SYSTEM DATABASE
system-containers database-containers
2005 IBM Corporation
IBM Software Group
System Containers
SMS Containers USING (container string)
DMS Containers
USING (FILE container string number of pages) (DEVICE container string number of pages)
2005 IBM Corporation
IBM Software Group
Table Space Example
CREATE TABLESPACE TS1 MANAGED BY SYSTEM USING (/home/inst01/database/ts1)
CREATE TABLESPACE DMS01D MANAGED BY DATABASE
USING (FILE C:\DMS\DATABASE\DMS01D 1000)
10
2005 IBM Corporation
IBM Software Group
Create Tables Command Line db2 create table ARTISTS ( ARTNO NAME SMALLINT NOT NULL, VARCHAR(40),
COMPANY CHAR(20), BIO CLOB (10K) NOT LOGGED,
PICTURE BLOB (2700K) NOT LOGGED )
IN DMS01D INDEX IN DMS01I LONG IN DMS01L
11
2005 IBM Corporation
IBM Software Group
Create Table Script File
create table warehouse (itemno smallint, warehousename char(20), qty integer ) in dms03d;
The script file cr_warehouse contains the above info db2 tvf cr_warehouse
12
2005 IBM Corporation
IBM Software Group
Table Characteristics Describe command db2 describe table ARTISTS
Column Type Type name schema name Length Scale Nulls --------------- --------- ------------------ -------- ----- -----ARTNO SYSIBM SMALLINT 2 0 No NAME SYSIBM VARCHAR 40 0 Yes COMPANY SYSIBM CHARACTER 20 0 Yes BIO SYSIBM CLOB 20480 0 Yes PICTURE SYSIBM BLOB 2764800 0 Yes
13
2005 IBM Corporation
IBM Software Group
Describe Indexes
14
2005 IBM Corporation
IBM Software Group
Moving Data
2005 IBM Corporation
IBM Software Group
Moving Data
IMPORT
EXPORT LOAD
db2move
16
2005 IBM Corporation
IBM Software Group
Import / Export
Import
File
Export
17
2005 IBM Corporation
IBM Software Group
IMPORT utility
IMPORT FROM filename OF
IXF
DEL ASC
LOBS FROM lob-path MODIFIED BY options MESSAGES
INSERT INTO
INSERT_UPDATE
table-name
REPLACE
18
REPLACE_CREATE
2005 IBM Corporation
IBM Software Group
Export
EXPORT TO file OF IXF DEL WSF MESSAGES message-file
select statement
19
2005 IBM Corporation
IBM Software Group
LOAD 1) Load Loads data, collects index keys 2) Build creates the indexes
3) Delete Delete unique key violations place into exception tables.
4) Index Copy copy indexes from temp table space
20
2005 IBM Corporation
IBM Software Group
LOAD Command LOAD FROM filename OF IXF
ASC DEL LOBS FROM lob-path MESSAGES INSERT REPLACE MODIFIED BY options
message-file INTO table-name
RESTART
TERMINATE
21
2005 IBM Corporation
IBM Software Group
LOAD from Cursor
Create nickname sales for another database SAMPLE table SALES
Create nickname employee for
another database SAMPLE table EMPLOYEE DECLARE C1 CURSOR FOR SELECT SALES.SALES_PERSON, LASTNAME, FIRSTNME FROM SALES, EMPLOYEE WHERE SALES_PERSON = [Link]
LOAD FROM C1 OF CURSOR INSERT INTO LOCAL_SALES
22
2005 IBM Corporation
IBM Software Group
DB2MOVE
db2move
Database
db2mov e
[Link]
[Link]
23
2005 IBM Corporation
IBM Software Group
db2move syntax db2move database-name import export
load
tc table-creators
tn table-name
sn schema-names ts
24
table space-names
2005 IBM Corporation
IBM Software Group
GUI Tools
Using Graphical User Interface
2005 IBM Corporation
IBM Software Group
Using the Control Center
26
2005 IBM Corporation
IBM Software Group
Control Center Create Database
27
2005 IBM Corporation
IBM Software Group
Examples of Table Space
CREATE TABLESPACE TS1 MANAGED BY SYSTEM USING (C:\SMS\MUSICKEG\TS1) CREATE TABLESPACE DMS01D MANAGED BY DATABASE USING (FILE C:\DMS\MUSICKEG\DMS01D 161) EXTENTSIZE 8 PREFETECHSIZE 8 CREATE TABLESPACE DMS01I MANAGED BY DATABASE USING (FILE C:\DMS\MUSICKEG\DMS01I 48) EXTENTSIZE 4 PREFETCHSIZE 4
28
2005 IBM Corporation
IBM Software Group
Table Space Using GUI
29
2005 IBM Corporation
IBM Software Group
Table Space Using GUI (2)
30
2005 IBM Corporation
IBM Software Group
Table Space Using GUI (3)
31
2005 IBM Corporation
IBM Software Group
Table Space Using GUI (4)
32
2005 IBM Corporation
IBM Software Group
Command Editor
33
2005 IBM Corporation
IBM Software Group
Visual Explain
34
2005 IBM Corporation
IBM Software Group
Visual Explain
35
2005 IBM Corporation
IBM Software Group
Task Center
36
2005 IBM Corporation
IBM Software Group
Journal
37
2005 IBM Corporation
IBM Software Group
RECOVERY
2005 IBM Corporation
IBM Software Group
Recovery
Defining logs Recovery of database
Recovery of a table space
Offline versus Online
39
2005 IBM Corporation
IBM Software Group
Database Recovery LOGRETAIN = NO
Database
[Link]
[Link] (Secondary Log)
[Link]
[Link] (Secondary Log)
[Link]
40
2005 IBM Corporation
IBM Software Group
Database Recovery LOGRETAIN = RECOVERY
Database
[Link]
[Link]
[Link]
[Link]
[Link]
41
2005 IBM Corporation
IBM Software Group
Backup
If LOGRETAIN = Recovery you may backup table space or database
If LOGRETAIN = NO you may only backup database
BACKUP DB database-name ONLINE to C:\backup
INCLUDE LOGS
42
2005 IBM Corporation
IBM Software Group
Recovery
If LOGRETAIN = NO, you may only recover the database
If LOGRETAIN = RECOVERY, you may recover a table space or a database from a full database backup
43
2005 IBM Corporation
IBM Software Group
Modes of Recovery
Offline Online RESTORE DB database-name FROM file TAKEN AT time
ROLLFORWARD DATABASE database-name
TO isotime
END OF LOGS
AND STOP
44
2005 IBM Corporation
IBM Software Group
HADR High Availability Disaster Recovery
Database1
Laptop computer
Database_standby
45
2005 IBM Corporation
IBM Software Group
Performance and Tuning
2005 IBM Corporation
IBM Software Group
Performance and Tuning
Database Configuration parameters
Database Structure SQL Statements
47
2005 IBM Corporation
IBM Software Group
Database Configuration parameters
48
2005 IBM Corporation
IBM Software Group
Configuration Parameters
49
2005 IBM Corporation
IBM Software Group
Buffer Pool
Select * f rom Staf f
Buf f er Pool
Database
50
2005 IBM Corporation
IBM Software Group
Monitoring Buffer Pool Hit Ratio
db2 get snapshot for bufferpools on database-name
Bufferpool name Database name Database path Input database alias Snapshot timestamp Buffer pool data logical reads = IBMDEFAULTBP = MUSICKEG = C:\DB2\NODE0000\SQL00002\ = MUSICKEG = 05/04/2005 [Link].329018 = 336
Buffer pool data physical reads = 129
51
2005 IBM Corporation
IBM Software Group
Sort Heap
Sorts are done in sortheap
If no space for sort data is moved to TEMPSPACEn GET SNAPSHOT FOR ALL ON database
52
2005 IBM Corporation
IBM Software Group
Package Cache
Dynamic SQL statements
Select * from Staff w here ID = 10
Select * from Staff where ID = 10
Update Staff Set Salary = Salary + 100 w here ID = 10 Select * from EMPLOYEE
Package Cache
53
2005 IBM Corporation
IBM Software Group
LOCK
Locks are held to prevent loss of data
Lock Row / Table / Table Space LOCKLIST
MAXLOCKS
ALTER TABLE table-name LOCKSIZE TABLE
54
2005 IBM Corporation
IBM Software Group
Show Locks
55
2005 IBM Corporation
IBM Software Group
SQL Statements
Determine which statement is causing the majority of problems
Determine what might be causing the problem
Testing the solution
56
2005 IBM Corporation
IBM Software Group
SQL Activity Monitor
57
2005 IBM Corporation
IBM Software Group
Visual Explain
58
2005 IBM Corporation
IBM Software Group
Table Statistics
59
2005 IBM Corporation
IBM Software Group
Performance Summary
Buffer pools
Numerous Database Configuration parameters SQL Statement Tuning
60
2005 IBM Corporation
IBM Software Group
Security
2005 IBM Corporation
IBM Software Group
Security
Security is used at the operating system level
Table access is through the database
62
2005 IBM Corporation
IBM Software Group
Instance Level
SYSADM_GROUP
SYSCTRL_GROUP SYSMAINT_GROUP
SYSMON_GROUP
63
2005 IBM Corporation
IBM Software Group
GRANT / REVOKE
GRANT access to an object/program
REVOKE access to an object/program GRANT SELECT ON TABLE ARTISTS TO USER1
64
2005 IBM Corporation
IBM Software Group
Summary
The Relational Database can be simple or complex
The database structure is simple, Table spaces, Tables, etc. Recovery is straight forward
Database maintenance can be automated
Tuning the database is a life long endeavor
65
2005 IBM Corporation
IBM Software Group
DB2 UDB Proof of Technology
July 12, 13 DB2 UDB Administration Proof of Technology
IBM McClean Tec 8401 Greensboro Drive
McClean, VA 22102
Suite 120 First Floor WebSphere Information Integrator July 14, 2005 Contact: Keith E. Gardenhire keithgar@[Link]
66
2005 IBM Corporation
IBM Software Group
DB2 UDB Proof of Technology Cont
67
2005 IBM Corporation