Download - DB2 UDB - International Informix Users Group
![Page 2: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/2.jpg)
IBM Software Group
© 2005 IBM Corporation2
Introduction
DB2 UDB V8.2
Basic Database Administration
![Page 3: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/3.jpg)
IBM Software Group
© 2005 IBM Corporation3
Products
DB2 Personal Edition
DB2 Workgroup Server Edition
DB2 Enterprise Server Edition
DB2 Workgroup Server Unlimited Edition
DB2 UDB Express Edition
![Page 4: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/4.jpg)
IBM Software Group
© 2005 IBM Corporation4
Data Partitioning Feature
Data Data Data Data
Ethernet
Node 1 Node 2 Node 3 Node 4
![Page 5: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/5.jpg)
IBM Software Group
© 2005 IBM Corporation5
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
![Page 6: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/6.jpg)
IBM Software Group
© 2005 IBM Corporation6
Creating the Database
Create db database-name
Creates three table spaces
Creates System Catalog Tables
![Page 7: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/7.jpg)
IBM Software Group
© 2005 IBM Corporation7
Create Database Command
CREATE DATABASE dbname AS alias_name
![Page 8: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/8.jpg)
IBM Software Group
© 2005 IBM Corporation8
Table SpaceLogical 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
TEMPORARY
USER
MANAGED BY SYSTEM system-containers
DATABASE database-containers
![Page 9: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/9.jpg)
IBM Software Group
© 2005 IBM Corporation9
System ContainersSMS Containers
USING (‘container string’)
DMS Containers
USING (FILE ‘container string’ number of pages)
(DEVICE ‘container string’ number of pages)
![Page 10: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/10.jpg)
IBM Software Group
© 2005 IBM Corporation10
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)
![Page 11: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/11.jpg)
IBM Software Group
© 2005 IBM Corporation11
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
![Page 12: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/12.jpg)
IBM Software Group
© 2005 IBM Corporation12
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
![Page 13: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/13.jpg)
IBM Software Group
© 2005 IBM Corporation13
Table CharacteristicsDescribe command
db2 describe table ARTISTSColumn Type Typename schema name Length Scale Nulls--------------- --------- ------------------ -------- ----- ------ARTNO SYSIBM SMALLINT 2 0 NoNAME SYSIBM VARCHAR 40 0 YesCOMPANY SYSIBM CHARACTER 20 0 YesBIO SYSIBM CLOB 20480 0 YesPICTURE SYSIBM BLOB 2764800 0 Yes
![Page 14: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/14.jpg)
IBM Software Group
© 2005 IBM Corporation14
Describe Indexes
![Page 15: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/15.jpg)
IBM Software Group
© 2005 IBM Corporation
Moving Data
![Page 16: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/16.jpg)
IBM Software Group
© 2005 IBM Corporation16
Moving Data
IMPORT
EXPORT
LOAD
db2move
![Page 17: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/17.jpg)
IBM Software Group
© 2005 IBM Corporation17
Import / Export
FileImport
Export
![Page 18: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/18.jpg)
IBM Software Group
© 2005 IBM Corporation18
IMPORT utility
IMPORT FROM filename OF IXF
DEL
ASC
LOBS FROM lob-path MODIFIED BY options
MESSAGES
INSERT INTO table-name
INSERT_UPDATE
REPLACE
REPLACE_CREATE
![Page 19: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/19.jpg)
IBM Software Group
© 2005 IBM Corporation19
Export
EXPORT TO file OF IXF MESSAGES message-file
DEL
WSF
select statement
![Page 20: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/20.jpg)
IBM Software Group
© 2005 IBM Corporation20
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
![Page 21: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/21.jpg)
IBM Software Group
© 2005 IBM Corporation21
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
![Page 22: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/22.jpg)
IBM Software Group
© 2005 IBM Corporation22
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
![Page 23: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/23.jpg)
IBM Software Group
© 2005 IBM Corporation23
db2move
db2move
Database
db2move.lst
table.ixf
DB2MOVE
![Page 24: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/24.jpg)
IBM Software Group
© 2005 IBM Corporation24
db2move syntax
db2move database-name import
export
load
tc table-creators
tn table-name
sn schema-names
ts table space-names
![Page 25: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/25.jpg)
IBM Software Group
© 2005 IBM Corporation
GUI Tools Using Graphical User Interface
![Page 26: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/26.jpg)
IBM Software Group
© 2005 IBM Corporation26
Using the Control Center
![Page 27: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/27.jpg)
IBM Software Group
© 2005 IBM Corporation27
Control Center – Create Database
![Page 28: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/28.jpg)
IBM Software Group
© 2005 IBM Corporation28
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
![Page 29: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/29.jpg)
IBM Software Group
© 2005 IBM Corporation29
Table Space Using GUI
![Page 30: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/30.jpg)
IBM Software Group
© 2005 IBM Corporation30
Table Space Using GUI (2)
![Page 31: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/31.jpg)
IBM Software Group
© 2005 IBM Corporation31
Table Space Using GUI (3)
![Page 32: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/32.jpg)
IBM Software Group
© 2005 IBM Corporation32
Table Space Using GUI (4)
![Page 33: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/33.jpg)
IBM Software Group
© 2005 IBM Corporation33
Command Editor
![Page 34: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/34.jpg)
IBM Software Group
© 2005 IBM Corporation34
Visual Explain
![Page 35: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/35.jpg)
IBM Software Group
© 2005 IBM Corporation35
Visual Explain
![Page 36: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/36.jpg)
IBM Software Group
© 2005 IBM Corporation36
Task Center
![Page 37: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/37.jpg)
IBM Software Group
© 2005 IBM Corporation37
Journal
![Page 38: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/38.jpg)
IBM Software Group
© 2005 IBM Corporation
RECOVERY
![Page 39: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/39.jpg)
IBM Software Group
© 2005 IBM Corporation39
Recovery
Defining logs
Recovery of database
Recovery of a table space
Offline versus Online
![Page 40: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/40.jpg)
IBM Software Group
© 2005 IBM Corporation40
Database Recovery LOGRETAIN = NO
Database
S0000000.log
S0000001.log
S00000002.log
S0000003.log (Secondary Log)
S0000004.log (Secondary Log)
![Page 41: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/41.jpg)
IBM Software Group
© 2005 IBM Corporation41
Database Recovery LOGRETAIN = RECOVERY
Database
S0000000.log
S0000001.log
S00000002.log
S0000003.log
S0000004.log
![Page 42: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/42.jpg)
IBM Software Group
© 2005 IBM Corporation42
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
![Page 43: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/43.jpg)
IBM Software Group
© 2005 IBM Corporation43
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
![Page 44: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/44.jpg)
IBM Software Group
© 2005 IBM Corporation44
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
![Page 45: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/45.jpg)
IBM Software Group
© 2005 IBM Corporation45
HADR – High Availability Disaster Recovery
Database_standby
Database1
Laptop computer
![Page 46: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/46.jpg)
IBM Software Group
© 2005 IBM Corporation
Performance and Tuning
![Page 47: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/47.jpg)
IBM Software Group
© 2005 IBM Corporation47
Performance and Tuning
Database Configuration parameters
Database Structure
SQL Statements
![Page 48: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/48.jpg)
IBM Software Group
© 2005 IBM Corporation48
Database Configuration parameters
![Page 49: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/49.jpg)
IBM Software Group
© 2005 IBM Corporation49
Configuration Parameters
![Page 50: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/50.jpg)
IBM Software Group
© 2005 IBM Corporation50
Buffer Pool
Database
Buffer Pool
Select * from Staff
![Page 51: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/51.jpg)
IBM Software Group
© 2005 IBM Corporation51
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
![Page 52: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/52.jpg)
IBM Software Group
© 2005 IBM Corporation52
Sort Heap
Sorts are done in sortheap
If no space for sort data is moved to TEMPSPACEn
GET SNAPSHOT FOR ALL ON database
![Page 53: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/53.jpg)
IBM Software Group
© 2005 IBM Corporation53
Package Cache
Dynamic SQL statements
Package Cache
Select * from Staff where ID = 10Select * from Staff where ID = 10
Update Staff Set Salary = Salary + 100 where ID = 10
Select * from EMPLOYEE
![Page 54: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/54.jpg)
IBM Software Group
© 2005 IBM Corporation54
LOCK
Locks are held to prevent loss of data
Lock Row / Table / Table Space
LOCKLIST
MAXLOCKS
ALTER TABLE table-name LOCKSIZE TABLE
![Page 55: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/55.jpg)
IBM Software Group
© 2005 IBM Corporation55
Show Locks
![Page 56: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/56.jpg)
IBM Software Group
© 2005 IBM Corporation56
SQL Statements
Determine which statement is causing the majority of problems
Determine what might be causing the problem
Testing the solution
![Page 57: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/57.jpg)
IBM Software Group
© 2005 IBM Corporation57
SQL Activity Monitor
![Page 58: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/58.jpg)
IBM Software Group
© 2005 IBM Corporation58
Visual Explain
![Page 59: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/59.jpg)
IBM Software Group
© 2005 IBM Corporation59
Table Statistics
![Page 60: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/60.jpg)
IBM Software Group
© 2005 IBM Corporation60
Performance Summary
Buffer pools
Numerous Database Configuration parameters
SQL Statement Tuning
![Page 61: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/61.jpg)
IBM Software Group
© 2005 IBM Corporation
Security
![Page 62: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/62.jpg)
IBM Software Group
© 2005 IBM Corporation62
Security
Security is used at the operating system level
Table access is through the database
![Page 63: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/63.jpg)
IBM Software Group
© 2005 IBM Corporation63
Instance Level
SYSADM_GROUP
SYSCTRL_GROUP
SYSMAINT_GROUP
SYSMON_GROUP
![Page 64: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/64.jpg)
IBM Software Group
© 2005 IBM Corporation64
GRANT / REVOKE
GRANT access to an object/program
REVOKE access to an object/program
GRANT SELECT ON TABLE ARTISTS TO USER1
![Page 65: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/65.jpg)
IBM Software Group
© 2005 IBM Corporation65
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
![Page 66: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/66.jpg)
IBM Software Group
© 2005 IBM Corporation66
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
![Page 67: DB2 UDB - International Informix Users Group](https://reader033.vdocuments.us/reader033/viewer/2022061221/54be79c64a7959af118b45ee/html5/thumbnails/67.jpg)
IBM Software Group
© 2005 IBM Corporation67
DB2 UDB Proof of Technology Con’t