Informix DB2 Comparison

1
Informix DB2 Comparison
2
Introduction

• Comparing DB2(EE) to Informix (ODS Version 7)
• Architecture
• Tablespaces/Dbspaces
• Tables
• Indexes
• Locks/Concurrency
• Clients
• Utilities
• Monitoring/Analysis

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 database parameters at the instance
  level.
• Both database multi-threaded. DB2 in v7.

4
Tablespace/Dbspaces..

• Databases organized into a collection of
  tablespaces.
• Organized as dbspaces
• Tablespace comprised of containers.
• Chunks
• Containers made up of either Raw or Cooked
  Volumes
• Chunks the same.
• Containers written to in a round-robin fashion.
• Size of extents set at tablespace create for all
  tables within the tablespace.
• Extent size set at table level.

5
..Tablespace/Dbspaces

• Tablespace backed by bufferpool.
• - None for Informix
• May have different extent size tablespaces
• associated bufferpools

6
Tables..

• Table in only one tablespace.
• Table may span multiple dbspaces. --
  Fragmentaion
• May create not-logged tables.
• No temporary table
• Created in temp dbspace if table created as
  not-logged.
• Clustered tables in DB2.
• Two Extents allocated at creation
• Number of extents guided by first and next extents

7
..Tables

• 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 datetime fraction(5)
• VARCHAR FOR BIT binary

8
Indexes

• Indexes may be in separate tablespaces than the
  table -- only DMS.
• Clustering index maintains data in the table.
• Only on the alter or create of a clustered index.

9
Locks/Concurrency

• Row level locking is the default.
• Locking specified at table creation (defaults to
  page).
• Lock Parameters, Locklist and MaxLocks.
• LOCKS.
• Lock escalation may occur.
• No Lock Escalations - overflow errors result in
  rollback
• Explicit locking allowed.
• RR, RS, CS(default), UR
• RR (default for ANSI), CS, CR(default for
  non-ANSI), DR

10
Clients

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

11
Utilities ..

• Backup - ADSM/Disk/Tape
• Onbar/Disk/Tape
• Recovery
• Load - db2move
• DBLOAD
• HPL
• Import
• DBIMPORT
• Export
• DBEXPORT
• Reorgchk
• Reorg

12
..Utilities

• Runstats
• Update Stats
• DB2LOOK
• dbschema

13
Monitoring/Analysis

• Snapshot monitoring
• ONSTAT
• Event monitoring
• ONSTAT,I-SPY
• Explain
• Db2advis