![Page 1: Introduction to IBM DB2 Keith T. Weber GIS Director- Idaho State University](https://reader036.vdocuments.us/reader036/viewer/2022062404/5519b3c05503465b578b46dc/html5/thumbnails/1.jpg)
Introduction to IBM DB2
Introduction to IBM DB2
Keith T. WeberGIS Director- Idaho State
University
![Page 2: Introduction to IBM DB2 Keith T. Weber GIS Director- Idaho State University](https://reader036.vdocuments.us/reader036/viewer/2022062404/5519b3c05503465b578b46dc/html5/thumbnails/2.jpg)
What is it?What is it?• IBM• DB2 (Database 2)• An object-relational
database: – No software limitations (e.g.,
size of database, number of tables, number of entities per table).
– Very scalable (it can grow to fit your needs).
– Efficient for numerous concurrent clients
![Page 3: Introduction to IBM DB2 Keith T. Weber GIS Director- Idaho State University](https://reader036.vdocuments.us/reader036/viewer/2022062404/5519b3c05503465b578b46dc/html5/thumbnails/3.jpg)
Concurrent ClientsConcurrent Clients• Enterprise GIS
– Potential concurrent clients
![Page 4: Introduction to IBM DB2 Keith T. Weber GIS Director- Idaho State University](https://reader036.vdocuments.us/reader036/viewer/2022062404/5519b3c05503465b578b46dc/html5/thumbnails/4.jpg)
Server InstallationServer Installation
• The amount of random-access memory (RAM) that required to run any edition of DB2 is 512 MB. – 1 GB is recommended for improved
performance• Required disk space depends on
type of installation and type of file system
![Page 5: Introduction to IBM DB2 Keith T. Weber GIS Director- Idaho State University](https://reader036.vdocuments.us/reader036/viewer/2022062404/5519b3c05503465b578b46dc/html5/thumbnails/5.jpg)
Professional TipsProfessional Tips
• Administration password– DBADMIN– ADMINISTRATOR
![Page 6: Introduction to IBM DB2 Keith T. Weber GIS Director- Idaho State University](https://reader036.vdocuments.us/reader036/viewer/2022062404/5519b3c05503465b578b46dc/html5/thumbnails/6.jpg)
DB2 EditionsDB2 Editions
![Page 7: Introduction to IBM DB2 Keith T. Weber GIS Director- Idaho State University](https://reader036.vdocuments.us/reader036/viewer/2022062404/5519b3c05503465b578b46dc/html5/thumbnails/7.jpg)
Personal ConfigurationPersonal Configuration• Personal Edition
– DB2 Personal Edition is a single-user version of the full DB2 product. • Has most features available in Express Edition• Remote clients cannot connect to this edition• Can be remotely administered with DB2 administration
tools• Net Search Extender• Spatial Extender
![Page 8: Introduction to IBM DB2 Keith T. Weber GIS Director- Idaho State University](https://reader036.vdocuments.us/reader036/viewer/2022062404/5519b3c05503465b578b46dc/html5/thumbnails/8.jpg)
Express ConfigurationExpress Configuration• Express Edition
– Built in autonomic manageability features– Supports high availability (HA) architectures– Net Search and Spatial Extenders– pureXML– Homogeneous federation– Scalable with Features Packs
![Page 9: Introduction to IBM DB2 Keith T. Weber GIS Director- Idaho State University](https://reader036.vdocuments.us/reader036/viewer/2022062404/5519b3c05503465b578b46dc/html5/thumbnails/9.jpg)
Workgroup ConfigurationWorkgroup
Configuration• Workgroup Server Edition
– Contains all of Express Edition– Includes High Availability Feature Pack
• Online table reorganizations• Tivoli System Automation (TSA) high
availabiltiy services• Support for DB2 advanced copy services• High Availability Disaster Recovery (HADR)
![Page 10: Introduction to IBM DB2 Keith T. Weber GIS Director- Idaho State University](https://reader036.vdocuments.us/reader036/viewer/2022062404/5519b3c05503465b578b46dc/html5/thumbnails/10.jpg)
Enterprise Configuration
Enterprise Configuration
• Enterprise Server Edition– DB2 Enterprise Edition is designed for
large databases with many users. It contains all the functionality of the Workgroup Edition, plus: • A license for an unlimited number of client
connections • Includes services for parallelism, MDCs, MQTs,
table partitioning and more• Scalable with additional Feature Packs
![Page 11: Introduction to IBM DB2 Keith T. Weber GIS Director- Idaho State University](https://reader036.vdocuments.us/reader036/viewer/2022062404/5519b3c05503465b578b46dc/html5/thumbnails/11.jpg)
Professional TipsProfessional Tips• DB2 licensed per CPU in your
server– Our Geoprocessing/SDE server is
a quad-processor.• Consider DBA overhead
– Oracle is DBA intensive– SQL Server least overhead– DB2 to date…
![Page 12: Introduction to IBM DB2 Keith T. Weber GIS Director- Idaho State University](https://reader036.vdocuments.us/reader036/viewer/2022062404/5519b3c05503465b578b46dc/html5/thumbnails/12.jpg)
DBADBA
• GUI based database administration
• Alternatively, command prompt can be used.
![Page 13: Introduction to IBM DB2 Keith T. Weber GIS Director- Idaho State University](https://reader036.vdocuments.us/reader036/viewer/2022062404/5519b3c05503465b578b46dc/html5/thumbnails/13.jpg)
Creating Databases/tables
Creating Databases/tables• Use the Control Center
• A database can be a new instance of DB2• The name you specify can only contain 1 to 8
characters. • To avoid potential problems:
– do not use the special characters @, #, and $ in a database name if you intend to have a client remotely connect to a host database.
– Also, because these characters are not common to all keyboards, do not use them if you plan to use the database in another country.
• On Windows NT-based systems (Vista, 7, 8, Server 2008, etc.), ensure that no instance name is the same as a service name.
![Page 14: Introduction to IBM DB2 Keith T. Weber GIS Director- Idaho State University](https://reader036.vdocuments.us/reader036/viewer/2022062404/5519b3c05503465b578b46dc/html5/thumbnails/14.jpg)
Checking Service Names
Checking Service Names
• C:\Windows\System32\drivers\etc
![Page 15: Introduction to IBM DB2 Keith T. Weber GIS Director- Idaho State University](https://reader036.vdocuments.us/reader036/viewer/2022062404/5519b3c05503465b578b46dc/html5/thumbnails/15.jpg)
DB2 SpecificsDB2 Specifics
• Pre-fetch• Buffer pools• Table data pages
![Page 16: Introduction to IBM DB2 Keith T. Weber GIS Director- Idaho State University](https://reader036.vdocuments.us/reader036/viewer/2022062404/5519b3c05503465b578b46dc/html5/thumbnails/16.jpg)
DB2 Data Types (Numeric)
DB2 Data Types (Numeric)
• FOR BIT DATA (boolean)• BYTE (0-255)• SMALLINT (-32,768 to
32,767 )• INTEGER (-2,147,483,648 to
2,147,483,647)• FLOAT <n> (2 types)• DOUBLE PRECISION <np,ns>
![Page 17: Introduction to IBM DB2 Keith T. Weber GIS Director- Idaho State University](https://reader036.vdocuments.us/reader036/viewer/2022062404/5519b3c05503465b578b46dc/html5/thumbnails/17.jpg)
DB2 Parameters in ArcGIS
DB2 Parameters in ArcGIS
• FLOAT < np,ns > – nprecision(total field length) = 1-6
– nscale (decimal places) = 1-6
– np,ns = 5,3 26.589 is OK, 256.381 is not
– Five (5) total characters 2 6 . 5 8 9
![Page 18: Introduction to IBM DB2 Keith T. Weber GIS Director- Idaho State University](https://reader036.vdocuments.us/reader036/viewer/2022062404/5519b3c05503465b578b46dc/html5/thumbnails/18.jpg)
Parameters (cont’d)Parameters (cont’d)
• DOUBLE PRECISION <np,ns>– np = 7 or more
– ns = 0 or more
![Page 19: Introduction to IBM DB2 Keith T. Weber GIS Director- Idaho State University](https://reader036.vdocuments.us/reader036/viewer/2022062404/5519b3c05503465b578b46dc/html5/thumbnails/19.jpg)
DB2 Data Types (Character)
DB2 Data Types (Character)
• CHARACTER<n>• VARCHAR<n>
![Page 20: Introduction to IBM DB2 Keith T. Weber GIS Director- Idaho State University](https://reader036.vdocuments.us/reader036/viewer/2022062404/5519b3c05503465b578b46dc/html5/thumbnails/20.jpg)
Parameters (cont’d)Parameters (cont’d)• CHARACTER<n>
– (AKA, String or Text)– Example a field named “URL” with n =
46– http://giscenter.isu.edu/training/
it4gis.htm
![Page 21: Introduction to IBM DB2 Keith T. Weber GIS Director- Idaho State University](https://reader036.vdocuments.us/reader036/viewer/2022062404/5519b3c05503465b578b46dc/html5/thumbnails/21.jpg)
DB2 Data Types (Special)
DB2 Data Types (Special)
• DATE• TIME• TIMESTAMP
![Page 22: Introduction to IBM DB2 Keith T. Weber GIS Director- Idaho State University](https://reader036.vdocuments.us/reader036/viewer/2022062404/5519b3c05503465b578b46dc/html5/thumbnails/22.jpg)
DB2 Data Types (Special)
DB2 Data Types (Special)
• Stored in special System managers tables– BLOB<n[K|M|G]>– CLOB<n[K|M|G]>– DBCLOB<n[K|M|
G]>– GRAPHIC<n>– VARGRAPHIC<n>
![Page 23: Introduction to IBM DB2 Keith T. Weber GIS Director- Idaho State University](https://reader036.vdocuments.us/reader036/viewer/2022062404/5519b3c05503465b578b46dc/html5/thumbnails/23.jpg)
Table Data PagesTable Data Pages
• All fields with standard data types for each record are contained within a single data page.
• There is a maximum of 255 records stored on each page.
• The ART of efficient data modeling is to minimize wasted pages while maximizing the proportion of each page written.
![Page 24: Introduction to IBM DB2 Keith T. Weber GIS Director- Idaho State University](https://reader036.vdocuments.us/reader036/viewer/2022062404/5519b3c05503465b578b46dc/html5/thumbnails/24.jpg)
An Instance ExampleAn Instance ExampleSPACE EFFICIENTY FOR 4kb PAGES
USED
WASTED
SPACE EFFIENCY FOR 8kb PAGES
USED
WASTED
number of fields
KB per record
page size
KB for 255 records
records at page size KB USED KB WASTED
100 GB TABLE SPACE
10 0.02 4 5.1 200 4 0 0 GB WASTED10 0.02 8 5.1 255 5.1 2.9 29 GB WASTED
![Page 25: Introduction to IBM DB2 Keith T. Weber GIS Director- Idaho State University](https://reader036.vdocuments.us/reader036/viewer/2022062404/5519b3c05503465b578b46dc/html5/thumbnails/25.jpg)
DB2 for GISDB2 for GIS• DB2 Spatial Extender lets you
integrate geographic data with your existing business data. It includes: – Data types such as points, lines, and
polygons – Functions such as area, endpoint, and
intersect – An indexing scheme for spatial data – This product is available for all editions of
DB2
![Page 26: Introduction to IBM DB2 Keith T. Weber GIS Director- Idaho State University](https://reader036.vdocuments.us/reader036/viewer/2022062404/5519b3c05503465b578b46dc/html5/thumbnails/26.jpg)
Questions?Questions?
![Page 27: Introduction to IBM DB2 Keith T. Weber GIS Director- Idaho State University](https://reader036.vdocuments.us/reader036/viewer/2022062404/5519b3c05503465b578b46dc/html5/thumbnails/27.jpg)
Key ConceptsKey Concepts
• Understand that while data is stored in tables, the tables span TABLE PAGES
• Understand what PRE-FETCH and CACHE are…and how they differ.
• Understand DB2 specific data types
![Page 28: Introduction to IBM DB2 Keith T. Weber GIS Director- Idaho State University](https://reader036.vdocuments.us/reader036/viewer/2022062404/5519b3c05503465b578b46dc/html5/thumbnails/28.jpg)
Your AssignmentYour Assignment
• Read IBM DB2 Ref (PDF).• Read Spatial Data Ext (PDF).• Complete the exercise
– Design table pages with the “DB2 Database Administration” exercise