DB2 Family Introduction

1. Products...........................................................................................................................................................2
1.1. DB2 Everyplace.......................................................................................................................................3
1.2. DB2 Express............................................................................................................................................3
1.3. DB2 Express-C........................................................................................................................................4
1.4. DB2 Personal Edition..............................................................................................................................4
1.5. DB2 Workgroup Server Edition..............................................................................................................4
1.6. DB2 Enterprise Server Edition................................................................................................................5
1.7. DB2 DWE (Data Warehouse Server Edition).........................................................................................6
1.8. DB2 Personal Developers Edition...........................................................................................................7
1.9. DB2 Universal Developers Edition.........................................................................................................7
1.10. DB2 Universal Developers Edition.....................................................................................................7
1.11. DB2 for i5 / OS....................................................................................................................................8
1.12. DB2 for z /OS......................................................................................................................................8
1.13. Other DB2 Products.............................................................................................................................8
1.13.1. DB2 Clients.....................................................................................................................................8
1.13.2. DB2 Connect. Win, Linux, UNIX, Mobile Apps, WS, & DB to iOS, zOS Svr/DB....................8
1.13.3. DB2 Extenders (user-defined datatypes and functions)..................................................................9
2. Concepts........................................................................................................................................................10
2.1. Servers, Instances and DBs. (hierarchical)............................................................................................10
2.2. Managing Instances...............................................................................................................................10
2.3. Creating DB2 9 Database. DB created via Wizard (in Control Center) or command (CLP)...............10
2.4. DB Objects.............................................................................................................................................11
2.5. Database Concurrency...........................................................................................................................11
2.5.1. Causes for DB Inconsistency:........................................................................................................11
2.5.2. Isolation Levels..............................................................................................................................11
2.6. LOCKS..................................................................................................................................................11
2.6.1. Lock attributes...............................................................................................................................12
2.6.2. Lock states (or modes)...................................................................................................................13
2.6.3. Locks & Performance....................................................................................................................14
2.7. Security..................................................................................................................................................14
2.7.1. Authentication................................................................................................................................14
2.7.2. Authority – after authentication.....................................................................................................14
2.7.3. DB Privileges.................................................................................................................................14
2.7.4. Object Privileges............................................................................................................................14
2.8. DWE vs OLTP Performance.................................................................................................................15

1
DB2 Family Introduction - Products

1. Products

 DB2 Everyplace
 DB2 Express
 DB2 Express-C
 DB2 Personal Edition
 DB2 Workgroup Server Edition
 DB2 Enterprise Server Edition
 DB2 Data Warehouse Edition
 DB2Personal Developers Edition
 DB2 Universal Developers Edition
 DB2 Enterprise Developers Edition
 DB2 for i5/OS
 DB2 for z/OS

2
DB2 Family Introduction - Products

1.1. DB2 Everyplace

 Small Footprint (~350 KB) RDBMS
 High performance data synchronization
 Enterprise apps extended to mobile devices
o PDAs, HPC (handheld PC), Smart Phones
 Implementations (mobile devices)
o Local RDBMS
o access to remote servers
o Embedded into mobile device
 Database Edition. For ISVs or s/w developers. Local DB
 Enterprise Edition. Synchronization server (secure) manages sync between mobile device and
backend RDBMS (Oracle, IDS, Sybase, SQLServer, DB2)

1.2. DB2 Express

 Entry level data server
 Microcomputers
o 2 CPUs / 4 GB RAM
o Supported o/s (Win, Linux, Solaris)
 Add-on Features (additional licensing fee)
o pureXML
o HA
 HADR (Disaster Recovery)
 Online Reorganization
 TSA MP (Tivoli System Automation Muti-Platform)
o Workload management. Connection Concentrator & (Query patroller or DB2 Governor)
 CC – fail-safe & load balancing
 QP (DWE) – load balancing – dynamically submit / execute queries. Create classes,
prioritize, schedule queries (off-hrs or ASAP), etc
 DB2G (DB2 EE)– rules to manage resources for applications – prioritize, etc. Same
as QP, but for apps
o Performance Optimization – for complex queries, OLAP apps & hi-perf for multiple
processor machines
 MQT – Materialized Query Tables. Table definition for result of query
 MDC - Multi-Dimensional Clustering
 Query Parallelism
o DB2 Homogenous Federation Feature. For customers requiring unified access to data
managed by multiple data servers
 Ability to manage & access remote DB2 / IDS data servers as if they were local tables
 Access diverse data (mainframe & distributed .. public / private) as if local table –
regardless of physical location

3
DB2 Family Introduction - Products

1.3. DB2 Express-C.

 No Charge Entry level data server
 Microcomputers
o 2 CPUs / 4 GB RAM
o Supported o/s (Win, Linux)
 For evaluation Purposes only
 Development of C/C++, Java, .NET, PHP and XQuery applications
 DB2 Express-C a subset of DB2 Express, except pureXML included with DB2 Express-C
 DB2 Express features NOI INCLUDED in DB2 Express-C include:,
o Spatial Extender Client & Samples
o MS Cluster Support
o IDS Data Source Support
o Replication Data Capture (both SQL Replication & SQLServer Replication)
o DB2 Web Tools
o Global Secure Toolkit
o APPC & NetBios Support
o DB2 Express Add-on Features (except pureXML)
 DB2 Express-C has seamless upgrade to DB2 Express

1.4. DB2 Personal Edition.

 Single User, full functionality
 Desktop / Laptop
 Managed Remotely. Good for occasionally connected or remote office implementations (single user)
o User create multiple local DBs
o Each DB use local disk, etc resources
o No remote client access
o Local DBs can be clients for other DB2 servers

1.5. DB2 Workgroup Server Edition.

 Full-function Client-Server RDBMS
 Microcomputers
o 4 CPUs / 16 GB RAM
o Win, Linux, UNIX
 Add-on Features (additional licensing fee)
o pureXML
o HA
o Workload management
o Performance Optimization
o DB2 Homogenous Federation Feature.

4
DB2 Family Introduction - Products

1.6. DB2 Enterprise Server Edition.

 Full-function Client-Server RDBMS, Web Enabled (Web-based BI)
 Scalable to hi-volume OLTP, DW (Terrabyte), mission critical apps (e.g. SAP) Mid-large Busin
 Any size computer (1 – 100’s of CPUs)
o Win, Linux, UNIX
 Enterprise Features include
o HADR
o Table Partitioning
o Online reorganization
o MQT
o Multi-Dimensional Data Clustering
o Full intra-query parallelism
o Connection Concentrator
o DB2 Governor
o TSA MP
 Packaged with DB2 Connect
o (DRDA) Distributed Relational Database Architecture protocol
o 5 users interface with
 iSeries, zSeries, IDS RDBMS
 non-DB hosts (CICS, VSAM, IMS
 Add-on Features (additional licensing fee)
o Storage Optimization – data Compression on disc
o Advanced Access Control
o DB2 database Partitioning Feature (DPF)
o Geodetic Data management
o DB2 Homogenous Federation Feature.
o Real-Time Insight (Hi vol OLTP management)

5
DB2 Family Introduction - Products

1.7. DB2 DWE (Data Warehouse Server Edition)

 Top-of-the-Line DB2 edition, for dynamic DWE.
o Enterprise Server Edition, with Data Partitioning Feature
o Data center environment - hi-volume OLTP, decision support merged into integrated IM
systems
o Core DW admin & construction components, Web Based apps with data mining / OLAP
 Core Components
o DW Core Engine
 DW Enhancing: MQT, optimizer, multi-dim clusters
 Partitioning: increased parallelsm for DBA / scalablty. (DB size, complex workloads)
o DWE Design Studio. Unified Graphical Dev Env to build BI solutions
 Xtnsion of RDA (Rat Data Architect) modeling – Eclipse Based
 Rev eng. Data models, SQL-based dataflows, mining flows
 Set-up OLAP cubes
 Deploy apps
o DWE Admin Console
 Web Based App - Manage / Monitor BI Apps
 Built on WAS – Web Clients to access/deploy DWE apps
o DWE OLAP Acceleration
 Relational structures adding OLAP (Drag & Drop)
 OLAP acceleration – analyze dimensional models / aggregation
 DBAs show aggregate graphical views of business activity
 End-users – Faster Cube Loads, drill down into data
o DWE Data Mining & Visualization
 No reqt to export to data mining compt, or use small data sets
 Data mining
 Input historical data into mathematical fns, output: business rules
 Business rules form a model
 Visualization
 Model Analysis via java-based results browser (for verification)
 Scoring – R/T predicting outcome (catches fraud. Records / defects)
 DWE Alphabox Analytics.
 Rapidly create custom (analysis)Web Apps
 Tightly integrated with DWE OLAP acceleration
 Query Patroller. Query management tool
 Dynamically control flow of queries
 Priority / Resources / category / trends & query optimization / tuning
 Schedule queries, depending on resources (ASAP, off hrs, suspend)

6
DB2 Family Introduction - Products

1.8. DB2 Personal Developers Edition

 Linux / Windows NOT FOR Production
 Components. App Dev can design, build, prototype single-user apps.
o DB2 Personal Edition
o DB2 Extenders
o DB2 Connect (Personal Edition)
o SDK
 Embedded SQL
 IBM Call level Interface (CLI). Comparable to MS ODBC
 DB2 APIs
 JDBC
 SQLJ
 .NET
 PHP
 PYTHON
 Pre-Compilers & Libraries

1.9. DB2 Universal Developers Edition

 Any supported platform (client or server) NOT FOR Production
 Components. App Dev can design, build, prototype single-user apps.
o DB2 Everyplace
o DB2 Express
o DB2 Personal Edition
o DB2 Workgroup Server Edition
o DB2 Enterprise Server Edition (+ add-ons)
o DB2 Extenders
o DB2 Connect (Personal Edition)
o DB2 Connect (Enterprise Edition)
o SDK (same as Personal Developers Edition)

1.10. DB2 Universal Developers Edition

 Any supported platform (client or server) NOT FOR Production
 Components. Includes DB2 Universal Developers Edition, PLUS
o Informix Data Server (IDS) Enterprise Edition
o Cloudscape
o DB2 Connect, unlimited edition for zSeries

7
DB2 Family Introduction - Products

1.11. DB2 for i5 / OS

 64-bit RDBMS
 Built directly into o/s. i5 Versioning numbering, NOT DB2 Versions
 iSeries navigator replace DB2 command center.
o Manage DB objects
o Referential integrity constrainst
o Reverse engineer DDL statements
o SQL Stored Procedures
o Manage DB Logs
o Analyze SQL performance (Explain)
 On-Demand for system i Features, such as Dynamic Logical Partitioning

1.12. DB2 for z /OS

 Full function RDBMS, designed specifically for z/OS
 Mainframe leadership in data / transactions for > 4 decades) Features include
o Hybrid data server for relational and XML data, plus services (e.g. pureXML)
o New datatypes (DECFLOAT, BIGINT, VARBINARY)
o Native SQL procedural lang
o Improved Security with roles, trusted content, new encryption functions
o DB2 extensions for z/OS v8
o Enhancements for large object support and performance
o Volume based copy and recover
o Refinements to optimization
o QMF interface design changed – allow interactive solns via Web Browser
o Enablement for system z Integrated Information Processors (IIP)
o DB2v9
 Cuts / Reduces IT costs significantly,
 Streamline efforts for compliance
 Simplify data serving on z9 o/s

1.13. Other DB2 Products

1.13.1. DB2 Clients

DB2 Runtime Client.

 Multi-o/s support.
 Remote RDBMS access.
 Basic Connectivity

DB2 Client. DB2 Runtime Client, Plus tools for c/s config, dba, app development
 Configuration Assistant.
 Control Center / other dba graphical tools (Linux & Windows Only)
 First Steps for New Users
 Applicatin Development Tools

8
DB2 Family Introduction - Products
1.13.2. DB2 Connect. Win, Linux, UNIX, Mobile Apps, WS, & DB to iOS, zOS Svr/DB
 DB2 Connect Personal Edition
 DB2 Connect Enterprise Edition
o Connects multiple users / workstations to mainframe / mini computer servers.
o Licensing based on concurrent users - not practical for multi-tier apps or web apps – std
client-server uses
 DB2 Connect App Server Edition
o DB2 Connect EE function
o Licensing based on # CPUs connecting to RDBM (mainframe or minicomputer).
 DB2 Connect Unlimited Edition.
o DB2 Connect EE function
o Licensing based on Millions of Service Units (MSUs) per hour Hi access to enterprise
data
Note: client communications to iOS, zOS – access DB2 or non-DB2 resources

1.13.3. DB2 Extenders (user-defined datatypes and functions)

 DB2 AVI (audio, video, image) Extender
 DB2 Text Extender
 DB2 Net Search Extender. App developers use Text Extender APIs in apps
 DB2 XML Extender. ‘Shred’s XML docs to col / table data, or stores entire doc in DB
 DB2 Spatial Extender.
o User-defined datatypes and Fns to store spatial data in DB2.
o Analyze spatial data (points, lines) and present in 3-D format (building loc, flood zones).
o Calc area, intersects, Integrate with business data (text & numbers)
 DB2 Geodetic Extender
o User-defined datatypes and Fns to for round earth (earths curvat).
o Geographic location analysis (DoD C&C, oceanography, etc)
o Global datasets, lat/long in 3 dimensions (ellipsoidal earth – geodetic)

Note: DB2v9 new XML features. XML datatype, new storage techniques for XML hierarchical
data, indexing to speed retrieval, SQL/XML enhancements, including native XQuery Support,
new capabilities for validating XML data, managing, changing XML Schemas.

9
DB2 Family Introduction - Concepts
2. Concepts

2.1. Servers, Instances and DBs. (hierarchical)

 Server. DB2 DB Manager – Background Process (Server). During Install, program files for DB2
DBM are copied to specific location and an instance is created
 Instances.
o Manage system resources & DB that are under their control.
o Share same DBManager Program & binaries, UNLESS DIFFERRENT VERSIONS OF DB2
INSTALLED.
o Environment – DB Manager Configuration file for instances
 DBs
o Managing storage
o Modification & retrieval of data
o Environment – DB Config file
o Set of grantable privileges and authorities – how users interact with data & objects
o DBA – DB composed of physical and logical components

2.2. Managing Instances

 Default Instance – defined by DB2INSTANCE ENV VARIABLE
 Reasons for multiple instances
o Separate Prod, Dev env
o Special Performance Tuning (for apps)
 System Commands (Instance level, at o/s, not CLP
o db2icrt [instancename] creates instance
o db2idroo [instancename] drop instance
o db2ilist [instancename] lists instance
o db2imigr [instancename] migrates instance to latest version
o db2iupd [instancename] updates instance to latest FP (also 32->64 bit conversion)
o db2start [instancename] starts instance
o db2stop [instancename] stops instance

2.3. Creating DB2 9 Database. DB created via Wizard (in Control Center) or command (CLP)
What Happens when DB created
 Directories / Subdirectories created.
 Files needed for management, monitoring & db recovery are created
 Buffer Pool created (IBMDEFAULTBP)
 2 tablespaces, and temp tablespace created (SYSCATSPACE, USERSPACE1, TEMPSPACE1)
o Database Managed TS (DMS)
o System Managed TS (SMS)
o Automatic managed TS
 system catalog tables and views are created. (Contains SQL for constraints but NOT views)
 DB is cataloged in system and local db directories (LIST DATABASE DIRCTORY cmd)
 DB configuration file is initialized
 4 schemas are created: SYSIBM, SYSCAT, SYSSTAT, SYSFUN (SYSIBM user owns all)
 Set of utility programs bound to DB
 Authorities & privileges granted to users

10
DB2 Family Introduction - Concepts
 Configuration Advisor launched (Performance tuning)

2.4. DB Objects
 Schemas (CREATE SCHEMA . AUTHORIZATION <sql statement >
 Tables
o constraints
o XML cols
o Identity Columns
o Partitioned Tables
 Views.
 Indexes.
 Aliases (Synonyms)
 Declared Temporary Tables
 Sequences.
 Triggers
 User-defined data types
o Distinct – std types, with checks, etc CREATE DISTINCT TYPE
o Structured – created by user – for typed tables CREATE TYPE
 User-defined functions
 Stored Procedures
o SQL stored procedures
o External stored procedures (C/C++, Java, COBOL)
 Packages

2.5. Database Concurrency

Sharing of resources by multiple (interactive) users and/or apps at the same time)

2.5.1. Causes for DB Inconsistency:

 Incomplete or uncommitted changes
 System crash in middle of changes
 DB Application terminates prematurely
 Multiple users access same data concurrently

2.5.2. Isolation Levels

Nonrepeat
Lost Dirty able
Isolatin Levels Updates Reads Reads Phantoms
Repeatable Read N N N N
Read Stability N N N N
Cursor Stability N N Y Y
Uncommitted Read N Y Y Y

11
DB2 Family Introduction - Concepts
2.6. LOCKS
Mechanism to associate a data resource with a single transaction, for sole purpose of controlling how other
transactions interact with that resource wile it is associated with transaction that has it locked. Transaction
“holds” or “owns” the lock

2.6.1. Lock attributes

 Object (TS, Tables, rows)
 Size (e.g. num of rows)
 Duration
 State (or mode)

12
DB2 Family Introduction - Concepts

2.6.2. Lock states (or modes)

Lock State Objects Lock Owner Actins Concurr Xaction access Other Locks Acq

Intent None (IN). TS, Tbl. Owner can read all (uncommitted) Other xactions can read, None
data, but cannot modify. Read-only modify. No delete
tables.
Intent Share TS, Tbl. Owner can read all (except Other xactions can read, Share (S) or Next Key
(IS). uncommitted) data, but can not modify Share (NS) lock - for
modify. no intent to modify.(not each row read
select for update, update, insert)
next key Share Row Owner can read all (except Other xactions can read, None
(NS) uncommitted) data, , but cannot NOT modify
modify. Used in place of share lock
(S) under isolation states of RS or
CS
Share (S) Tbl, Row Owner can read all (except Other xactions can read, individual rows, if tbl
uncommitted) data, but can not NOT modify not share locked
modify. no intent to modify .(not
select for update, update, insert)
running under RR isolation level
Intent Exclusive TS, Tbl. Owner can read all (except Other xactions can read S or NS lock acq for
(IX) uncommitted) data. For intent to & modify each row read. U and
modify .(select for update, update, X lock for each row to
insert) be updated
Share w/Intent Tbls Owner can read & modify all Other xactions can read X lock for each row to
Exclusive (SIX) (except uncommitted) data. NOT modify be updated
Typically for a S lock, acquiring X
lock (or vice-versa)
Update (U) Tbl, Row Owner can modify all (except Other xactions can read U tbl lock, X lock for
uncommitted) data. But can not NOT modify each row to be
read. For insert, update, delete updated
Next Key Weak Tbl, Row Owner can read all (except Other xactions can read None
Exclusive (NW) uncommitted) data. But can not & NOT modify
modify. Typically acq for next avail
row of a table when row inserted
into any index (not catalog)
Exclusive (X) Tbl, Row Owner can read & modify all xactions using UR iso lvl rows X locked, if table
(except uncommitted) data. read all (uncommitted), is not X locked
Typically xactions that select but can not modify.
(retrieve), then update,insert, delete Others NOT read, NOT
modify
Weak Exclusive Row Owner can read & modify all xactions using UR iso lvl None
(WE) (except uncommitted) data. read all (uncommitted),
Typically when ,insert row (non- but can not modify.
catalog/system table) Others NOT read, NOT
modify
Super Exclusive TS, Tbl. Owner can read & modify all xactions NOT read, NOT None
(Z) (except uncommitted) data. modify
Typically for alter, drop table.
Create / drop index or REORG
utility

13
DB2 Family Introduction - Concepts

2.6.3. Locks & Performance

 Lock Compatibility. Compatible if lock placed on same resource by another xaction
 Lock Conversion. DBM changing to a more restrictive lock. Typically row-level locks
o S, IX locks – neither more restrictive, so converted to SIX
 Lock Escalation. Session (App) has lock list – memory limit. If too many locks for xaction
(exceeding memory), then row level converted to table level locks (to free memory). Lock nto acq
until escalation complete. If can not free enuf memory, SQL Error. & rollback. (Alter table / Lock
table helps manage, but does not eliminate need for lock escalation)
 Lock waits & timeouts. Lock timeout detection feature of DBM – config parameter
 Deadlocks. Deadlock Cycle. Not addressed by timeout detection. Deadlock detector – DB
background process – wakes at pre-set intervals. Randomly rolls back one transaction & give SQL
error code.
o Conversion deadlock – deadlock on same resource xactions request new, incompatible
conversions. Two xactions perform index scan, then try to update same row
 Concurrency and granularity. Row level preferable to table locks

2.7. Security
Controlling Database Access. Combination of external security services and internal Access Control
 Level 1: Controls access to DB Instance
 Level 2: Controls to DB itself
 Level 3: Controls access to data and data objects in DB

2.7.1. Authentication
 External Service (o/s, add-on product or none (Win95/98/ME – user / pwd)
 Verifies User.

2.7.2. Authority – after authentication

 Group Only
 Group or User.
 User ONLY, NOT group (Security Administrator (SECADM))

Note: DBADM, SYSADM only privs that are allowed to access all data from all users. SYSADM at
Instance level, and DBADM at individual DB level
Note: SYSADM, SYSCTRL can create / drop DB (only ones)

2.7.3. DB Privileges
 CONNECT
 QUIESCE_
 IMPLICIT_SCHEMA.
 CREATETAB
 BINDADD
 CREATE_EXTERNAL_ROUTINE.
 CREATE_NOT_FENCED_ROUTINE.

2.7.4. Object Privileges

14
DB2 Family Introduction - Concepts
2.8. DWE vs OLTP Performance
 DWE
o Performance. Optimized for queries – Indexing (multiple indexes for performance)
o Scalability – h/w and s/w
o Availability – 24x7x365
o Admin – flexible, extensible, min admin costs of hi-vol RDBMS
 OLTP
o Hi-Perf Hi thruput (100’s / sec, sub-sec user response). Simple sql limit rows), simple
transactions. Min I/O, optimize CPU Util, eliminate sort, improve concurrency
o Hi Vol. Large volume of data, lots of concurrent users to 1000’s)
o HA 24x7x365

15