1 / 67

DB2 UDB the Basics

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.

rufina
Download Presentation

DB2 UDB the Basics

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DB2 UDB the Basics Keith E. Gardenhire keithgar@us.ibm.com

  2. DB2 UDB V8.2 Basic Database Administration Introduction

  3. Products DB2 Enterprise Server Edition DB2 Workgroup Server Unlimited Edition DB2 Workgroup Server Edition DB2 UDB Express Edition DB2 Personal Edition

  4. Data Partitioning Feature

  5. 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

  6. Creating the Database • Create db database-name • Creates three table spaces • Creates System Catalog Tables

  7. Create Database Command CREATE DATABASE dbname AS alias_name

  8. 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

  9. System Containers SMS Containers USING (‘container string’) DMS Containers USING (FILE ‘container string’ number of pages) (DEVICE ‘container string’ number of pages)

  10. 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)

  11. 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

  12. 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

  13. 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

  14. Describe Indexes

  15. Moving Data

  16. Moving Data • IMPORT • EXPORT • LOAD • db2move

  17. Import / Export Import File Export

  18. 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

  19. Export EXPORT TO file OF IXF MESSAGES message-file DEL WSF select statement

  20. 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

  21. 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

  22. 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

  23. DB2MOVE db2move db2move.lst table.ixf

  24. db2move syntax db2move database-name import export load tc table-creators tn table-name sn schema-names ts table space-names

  25. GUI Tools Using Graphical User Interface

  26. Using the Control Center

  27. Control Center – Create Database

  28. 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

  29. Table Space Using GUI

  30. Table Space Using GUI (2)

  31. Table Space Using GUI (3)

  32. Table Space Using GUI (4)

  33. Command Editor

  34. Visual Explain

  35. Visual Explain

  36. Task Center

  37. Journal

  38. RECOVERY

  39. Recovery Defining logs Recovery of database Recovery of a table space Offline versus Online

  40. Database Recovery LOGRETAIN = NO

  41. Database Recovery LOGRETAIN = RECOVERY

  42. 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

  43. 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

  44. 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

  45. HADR – High Availability Disaster Recovery

  46. Performance and Tuning

  47. Performance and Tuning • Database Configuration parameters • Database Structure • SQL Statements

  48. Database Configuration parameters

  49. Configuration Parameters

  50. Buffer Pool

More Related