IBM Software Group DB2 UDB the Basics Keith E. Gardenhire [email protected]

© 2005 IBM Corporation

IBM Software Group Introduction 

DB2 UDB V8.2



Basic Database Administration 2

© 2005 IBM Corporation

IBM Software Group Products

3

DB2 Enterprise Server Edition DB2 Workgroup Server Unlimited Edition DB2 Workgroup Server Edition DB2 UDB Express Edition DB2 Personal Edition © 2005 IBM Corporation

4

IBM Software Group Data Partitioning Feature Ethernet Node 1 Data Node 2 Data Node 3 Data Node 4 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 will start an instance db2stop will stop an instance

5

© 2005 IBM Corporation

6

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 7

© 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 TABLESPACE name LARGE SYSTEM USER TEMPORARY

8

MANAGED BY SYSTEM system-containers DATABASE 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)

9

© 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) © 2005 IBM Corporation

10

IBM Software Group Create Tables Command Line db2 create table ARTISTS ( ARTNO SMALLINT NOT NULL, NAME VARCHAR(40), COMPANY CHAR(20), BIO CLOB (10K) NOT LOGGED, PICTURE BLOB (2700K) NOT LOGGED ) IN DMS01D INDEX IN DMS01I LONG IN DMS01L © 2005 IBM Corporation

11

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 © 2005 IBM Corporation

12

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 File Import 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 table-name INSERT_UPDATE REPLACE

18

REPLACE_CREATE © 2005 IBM Corporation

IBM Software Group Export EXPORT TO file OF IXF MESSAGES message-file DEL WSF select statement © 2005 IBM Corporation

19

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 © 2005 IBM Corporation

20

IBM Software Group LOAD Command LOAD FROM filename OF IXF ASC DEL LOBS FROM lob-path MODIFIED BY options MESSAGES message-file INSERT INTO table-name REPLACE RESTART TERMINATE © 2005 IBM Corporation

21

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 = EMPLOYEE.LASTNAME

LOAD FROM C1 OF CURSOR INSERT INTO LOCAL_SALES © 2005 IBM Corporation

22

IBM Software Group DB2MOVE db2move db2move db2move.lst

table.ixf

23

Database © 2005 IBM Corporation

IBM Software Group db2move syntax db2move database-name import export load

24

tc table-creators tn table-name sn schema-names ts 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 © 2005 IBM Corporation

28

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

32

IBM Software Group Table Space Using GUI (4) © 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

Journal IBM Software Group

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 S0000000.log

S0000001.log

S00000002.log

S0000003.log (Secondary Log) S0000004.log (Secondary Log)

40

© 2005 IBM Corporation

IBM Software Group Database Recovery LOGRETAIN = RECOVERY Database S0000000.log

S0000001.log

S00000002.log

S0000003.log

S0000004.log

© 2005 IBM Corporation

41

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

© 2005 IBM Corporation

42

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

© 2005 IBM Corporation

43

IBM Software Group Modes of Recovery 

Offline



Online RESTORE DB database-name FROM file TAKEN AT time ROLLFORWARD DATABASE database-name TO isotime AND STOP END OF LOGS

© 2005 IBM Corporation

44

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 © 2005 IBM Corporation

50

IBM Software Group Monitoring Buffer Pool Hit Ratio 

db2 get snapshot for bufferpools on database-name Bufferpool name = IBMDEFAULTBP Database name = MUSICKEG Database path = C:\DB2\NODE0000\SQL00002\ Input database alias = MUSICKEG Snapshot timestamp = 05/04/2005 13:11:37.329018

Buffer pool data logical reads = 336 Buffer pool data physical reads = 129

© 2005 IBM Corporation

51

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

© 2005 IBM Corporation

52

IBM Software Group Package Cache 

Dynamic SQL statements

Select * from Staff where ID = 10

Select * from Staff where ID = 10 Update Staff Set Salary = Salary + 100 where ID = 10 Select * from EMPLOYEE

Package Cache © 2005 IBM Corporation

53

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

© 2005 IBM Corporation

56

57

IBM Software Group SQL Activity Monitor © 2005 IBM Corporation

58

IBM Software Group Visual Explain © 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

© 2005 IBM Corporation

60

Security IBM Software Group © 2005 IBM Corporation

IBM Software Group Security 

Security is used at the operating system level



Table access is through the database

© 2005 IBM Corporation

62

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

© 2005 IBM Corporation

64

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

© 2005 IBM Corporation

65

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



[email protected]

66

© 2005 IBM Corporation

IBM Software Group DB2 UDB Proof of Technology Con’t

67

© 2005 IBM Corporation