db2 udb the basics l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
DB2 UDB the Basics PowerPoint Presentation
Download Presentation
DB2 UDB the Basics

Loading in 2 Seconds...

play fullscreen
1 / 67

DB2 UDB the Basics - PowerPoint PPT Presentation


  • 123 Views
  • Uploaded on

DB2 UDB the Basics. Keith E. Gardenhire keithgar@us.ibm.com. DB2 UDB V8.2 Basic Database Administration. Introduction. Products. DB2 Enterprise Server Edition. DB2 Workgroup Server Unlimited Edition. DB2 Workgroup Server Edition. DB2 UDB Express Edition. DB2 Personal Edition.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'DB2 UDB the Basics' - rufina


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
db2 udb the basics

DB2 UDB the Basics

Keith E. Gardenhire

keithgar@us.ibm.com

introduction
DB2 UDB V8.2

Basic Database Administration

Introduction
products
Products

DB2 Enterprise Server Edition

DB2 Workgroup Server Unlimited Edition

DB2 Workgroup Server Edition

DB2 UDB Express Edition

DB2 Personal Edition

instance
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

creating the database
Creating the Database
  • Create db database-name
  • Creates three table spaces
  • Creates System Catalog Tables
create database command
Create Database Command

CREATE DATABASE dbname AS alias_name

table space
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

TEMPORARY

USER

MANAGED BY SYSTEM system-containers

DATABASE database-containers

system containers
System Containers

SMS Containers

USING (‘container string’)

DMS Containers

USING (FILE ‘container string’ number of pages)

(DEVICE ‘container string’ number of pages)

table space example
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)

create tables
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

create table script file
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

table characteristics
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

moving data16
Moving Data
  • IMPORT
  • EXPORT
  • LOAD
  • db2move
import export
Import / Export

Import

File

Export

import utility
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

export
Export

EXPORT TO file OF IXF MESSAGES message-file

DEL

WSF

select statement

slide20
LOAD
  • Load Loads data, collects index keys
  • Build creates the indexes
  • Delete Delete unique key violations place into exception tables.
  • Index Copy – copy indexes from temp table space
load command
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

load from cursor
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

db2move

DB2MOVE

db2move

db2move.lst

table.ixf

db2move syntax
db2move syntax

db2move database-name import

export

load

tc table-creators

tn table-name

sn schema-names

ts table space-names

gui tools

GUI Tools

Using Graphical User Interface

examples of table space
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

recovery39
Recovery

Defining logs

Recovery of database

Recovery of a table space

Offline versus Online

backup
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

recovery43
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
modes of recovery
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

performance and tuning47
Performance and Tuning
  • Database Configuration parameters
  • Database Structure
  • SQL Statements
monitoring buffer pool hit ratio
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

sort heap
Sort Heap
  • Sorts are done in sortheap
  • If no space for sort data is moved to TEMPSPACEn
  • GET SNAPSHOT FOR ALL ON database
package cache
Package Cache
  • Dynamic SQL statements
slide54
LOCK
  • Locks are held to prevent loss of data
  • Lock Row / Table / Table Space
  • LOCKLIST
  • MAXLOCKS
  • ALTER TABLE table-name LOCKSIZE TABLE
sql statements
SQL Statements
  • Determine which statement is causing the majority of problems
  • Determine what might be causing the problem
  • Testing the solution
performance summary
Performance Summary
  • Buffer pools
  • Numerous Database Configuration parameters
  • SQL Statement Tuning
security62
Security
  • Security is used at the operating system level
  • Table access is through the database
instance level
Instance Level
  • SYSADM_GROUP
  • SYSCTRL_GROUP
  • SYSMAINT_GROUP
  • SYSMON_GROUP
grant revoke
GRANT / REVOKE
  • GRANT access to an object/program
  • REVOKE access to an object/program
  • GRANT SELECT ON TABLE ARTISTS TO USER1
summary
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
db2 udb proof of technology
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@us.ibm.com