Informix DB2 UDB Comparison Presented By Nalini Subu

1
Informix DB2 UDB Comparison Pr
esented By Nalini Subu
2
Introduction

• Comparing DB2(EE) to Informix (Online Dynamic
  Server Version 7)
• Architecture
• Tablespaces/Dbspaces
• Tables
• Indexes
• Logical Logs
• Locks/Concurrency
• Clients
• Utilities
• Monitoring/Analysis
• Sources

3
Architecture

• Installation of DB2/Informix is an Instance.
• Instance parameters in DB2 controlled by the DBM
  CONFIG.
• Instance parameters in Informix controlled by
  ONCONFIG.
• One to many databases per instance.
• Each DB2 database has its own DB CONFIG.
• Informix parameters managed at the instance
  level.
• DB2 multi-threaded only on NT.
• Informix Multi-threaded both on Unix and NT.

4
Table(Db)spaces

• Databases organized into a collection of
  tablespaces.
• Organized as dbspaces
• Tablespace comprised of containers.
• Chunks
• Containers made up of either Raw or Cooked
  Volumes
• DMS or SMS Managed Tablespaces
• Extents allocated in a round-robin fashion among
  containers .
• Serial Allocation Of Extents
• Size of extents set at tablespace create for all
  tables within the tablespace.
• Extent size set at table level.

5
..Table(Db)spaces

• Tablespace backed by bufferpool.
• May have different page size tablespaces
  associated bufferpools
• Prefetching - NUM_IOSERVERS
• RA_PAGES/RA_THRESHOLD
• create db creates three default tablespaces
• syscatspace
• userspace
• tempspace

6
Tables

• Table in only one tablespace (Partitioning
  available on EEE)
• Table may span multiple dbspaces. --
  Fragmentation
• Two Extents allocated at creation
• Extent sizes guided by first and next extents
• Next Size doubles every 16 extents
• May create not-logged tables.
• Global temporary table
• Created in temp dbspace if table created as
  not-logged.
• Volatile Tables
• Summary Tables

7
Data Types

• Data types are different between the two.Some
  differences shown below
• UDB Informix
• CHAR FOR BIT binary
• DATE DATE
• DOUBLE float
• TIME datetime(time only)
• TIMESTAMP datetimefraction(5)
• VARCHAR FOR BIT binary
• User-defined datatypes

8
Indexes

• Indexes may be in separate tablespaces than the
  table -- only DMS.
• Attached and Detached Indexes
• Clustering index maintains data in cluster order
  for table.
• Only on the alter or create of a clustered index.
• Forward and Reverse Scans
• Include Columns in Unique Indexes

9
Locks / Concurrency

• Row level locking is the default.
• Locking specified at table creation (defaults to
  page).
• Lock Parameters Locklist (in pages) MaxLocks
• Locks
• Lock escalation may occur.
• No Lock Escalations - overflow errors result in
  rollback
• Explicit locking allowed.

10
...Locks / Concurrency

• RR, RS, CS(default), UR
• RR (default for ANSI), CS, CR(default for
  non-ANSI), DR
• Isolation Levels - JDBC/UDB
• JDBC DB2
• TRANSACTION_SERIALIZABLE RR
• TRANSACTION_REPEATABLE_READ RS
• TRANSACTION_READ_COMITTED CS
• TRANSACTION_DIRTY_UNCOMITTED UR

11
Logical Logs

• Logical logs allocated per database
• Allocated _at_ instance level
• Cooked FS
• Almost always raw
• Primary / Secondary Logs
• Default is circular logging
• Default is no logging
• Logs backed up with userexit
• Informix backup program in onconfig file
• Dual Logging

12
Clients

• May have static SQL against the database via a
  bind (packages)
• All SQL is dynamic
• Stored procedures may either be internal or
  external (Macro PSM)
• All procedures are internal and use SPL.
• No 4GL (Version 8?)
• 4GL
• GUI Stored Procedure Builder.
• Triggers cannot call Stored Procedures
• Can call Stored Procedures and also external
  programs

13
Clients

• Java Stored Procedures
• Control Center
• dbaccess/isql
• CLP
• Command Center
• Client Configuration Assistant

14
Utilities

• Backup - ADSM/Disk/Tape Onbar/Disk/Tape
• Recovery
• Redirected Restore
• Load , db2move (May put
  tablespace in load pending state or table in
  check pending state)
• dbload
• High Performance Loader
• Import
• dbimport

15
..Utilities

• Export
• dbexport
• Reorgchk
• Reorg
• hpl or unload/load
• Runstats
• Update Stats
• db2look
• dbschema
• db2batch

16
Monitoring/Analysis

• Snapshot monitoring (bufferpools,locks,sorts,sql
  statements)
• onstat
• Event monitoring
• onstat, I-Spy
• Explain
• (Explain without execution)
• set explain on
• Db2advis
• Dynexpln

17
Sources

• White Paper on Informix/Db2
• Practical Experience -)
• Email
• Nalini_Subu_at_homedepot.com