Informix 11.

7 Bootcamp
Informix Fundamentals
Information Management Technology Ecosystems

© 2010 IBM Corporation

Agenda

• Introduction To Informix

• Informix Editions

• Informix Architecture Review

• Appendix

© 2010 IBM Corporation

Agenda

• Introduction To Informix

• Informix Editions

• Informix Architecture Review

• Appendix

© 2010 IBM Corporation

Informix
“Set it and Forget it”

“…Intrado provides the core of U.S. 9-1-1… …for near hands free
administration, high availability
We needed a fast database; one that
and clustering for transaction
would respond immediately, every
intensive solutions
time…”

Resilient Agile Everywhere

Reliable Flexible Minimal
Secure Fast Affordable

Cost
Mission critical Effective
and life critical Solutions
solutions

1,000’s of 95% of telecom 20 of the top 25 Over 2,500 ISVs

Retailers worldwide service delivery US Supermarkets worldwide
providers

© 2010 IBM Corporation

 Extensive Platform Support

HP-UX on PA-RISC
and Itanium

Solaris on SPARC/x64

AIX on pSeries/iSeries
Linux on xSeries, X86-64
Windows 2003, Vista
zSeries, POWER and XP on x86/x64
Windows Server 2008

5 © 2010 IBM Corporation

 Informix Technical Roadmap – History of Features

2007 - 2008
2009
Scale-out at lower costs 2010
(Informix 11) Business Optimization
Application Integration
• Multi-node active cluster for • Informix Warehouse
high-availability (MACH) 11 • Grid Computing
• Cognos integration
• Multiple remote servers • Enhanced Warehouse capabilities
• Virtual Appliance
and shared disk cluster • Star/snowflake join
• Cloud Computing
• Open Admin tool (OAT) for support • Support for ODS environments
administration, SQL Admin
API • Online Storage • Improved optimizer statistics
2005 Optimization
• Improved checkpoint • Improved application
Optimized OLTP Engine performance • XPS features development environment
(Informix 10) • Secured data encryption, • External Tables • Stored procedure debugging
• Query performance LBAC, Common Criteria • Deeper Embed
• In memory support with
improvement certification
SolidDB • Automatic storage provisioning
• Online index build • Enhanced application • Replicate with other
development for SOA and • Automatic Fragmentation
• Multiple page size XML vendors via CDC • Embedability toolkit
support for better space
utilization • Web Feature Service support • Delayed Replication • Installation API
for geospatial applications
• Enhanced buffer • 4GL Enhancements • Even simpler administration
management • Optim Data Studio Support
• ConnectBy statement • Security
• Increased security with • Built-in Text Search support
column encryption • Fine-grained Auditing
• MQ Series support • Merge statement
• Disaster recovery with support • Trusted context
table level restore • Retail Integration Framework • Online Alter table • 4GL Enhancements

© 2010 IBM Corporation

 Informix Technical Roadmap – Future Enhancements

Flexible Grid
Development Performance
*Informix vNext+
2012
ƒ Warehouse/BI
improvements *Informix vNext+++
ƒ Benchmarks Applications Availability 2016…
ƒ Embed SolidDB into IDS
ƒ Informix for Security Continue to invest
Hadoop/Cloud
in Informix to
ƒ Informix for Handheld increase its
*Informix vNext++
devices strengths and to
2014
ƒ 4GL stored procedures open new markets
ƒDistributed query
ƒ Support for multi- processing
temperature data
ƒMaterialized Query Tables
ƒ Enhance Deep Embed
ƒExtend Grid to non-
ƒ Enhance Industry Offering database sources
(Utilities) * Features Subject to change

The information on the new product is intended to outline our general product direction and it should not be relied on in making a purchasing decision. The information on the new
product is for informational purposes only and may not be incorporated into any contract. The information on the new product is not a commitment, promise, or legal obligation to
deliver any material, code or functionality. The development, release, and timing of any features or functionality described for our products remains at our sole discretion.

© 2010 IBM Corporation

Agenda

• Introduction To Informix

• Informix Editions

• Informix Architecture Review

• Appendix

© 2010 IBM Corporation

 Informix Editions – *New* Packaging Starting With V11.50xC7

• Informix Developer Edition *free*

• Informix Innovator-C *free*

• Informix Choice Edition

• Informix Growth Edition

• Informix Ultimate Edition

* Informix Express Edition is not being withdrawn

9 © 2010 IBM Corporation

Mapping of Old Editions to New Editions

Old Edition New Edition

IDS Workgroup Edition Informix Growth Edition

IDS Enterprise Edition Informix Ultimate Edition

IDS Workgroup Edition Included in the Informix

advanced Access Control Growth Edition
IDS Enterprise Edition Included in the Ultimate
Advanced Access Control Edition
Informix Warehouse Informix Growth Edition
Workgroup Edition (includes Warehouse)
Informix Warehouse Informix Ultimate Edition +
Enterprise Edition Storage Optimization Feature
(includes Warehouse)

© 2010 IBM Corporation

 Informix Developer Edition
• A FREE edition of Informix 11.5 for developers to promote
application development and drive market share
• Full functionality - with scalability limits
• 1 CPU VP (i.e. 1 processor core)
• 1 GB shared memory, 8 GB database size
• 20 connections
• All Ultimate Edition Features
• Continuous Availability, Advanced Access Control, RSS, SDS, HDR,
etc.
• Includes CSDK, J/Foundation, OAT, AGS Server Studio, DBDK,
built-in blades, etc.
• For support, use the developerWorks Informix forum -
www.ibm.com/developerworks/forums/dw_forum.jsp?forum=1071&cat=19
11 © 2010 IBM Corporation
 Informix Innovator-C Edition
Free to deploy
• Self-managing data server for mid-market businesses
in production
• Provides the power, function and reliability with
simplicity in packaging, installation and deployment at no
investment cost

Supported OS Linux, Unix, Windows, MAC OS X

Restrictions 1 Socket / total 4 Cores

4 CPU VPs
2 GB shared memory maximum
Unavailable Compression, Parallel Features, Partitioning,
Features Advanced Access Control, Informix Warehouse

Partially HA: one primary with one HDR secondary

available (updatable!)
features
Enterprise Replication: two root nodes
Optional IBM Elite Support available for purchase. Redistribution requires a separate contract.
12 © 2010 IBM Corporation
 Informix Choice Edition
• Self-managing data server for mid-market businesses
• Provides the power, function and reliability with simplicity
in packaging, installation and deployment at no investment
cost
• Possibility to trade-up license

Supported OS Windows, MacOS

Restrictions 2 Sockets / total 8 Cores
8 GB shared memory maximum

Unavailable Compression, CAF (aka SDS), parallel

Features features, partitioning, LBAC, SQL
Warehousing Tool
Partially HA: one primary and one secondary (HDR or
available RSS), updatable
feature
ER: 2-root nodes

© 2010 IBM Corporation

 Informix Growth Edition
• Low-maintenance OLTP data server for growth enabled computing
• Useful for departments within large enterprises, midsized companies, ISVs for
OEM use
Supported OS Linux and Unix, Windows, Mac OS
Restrictions 4 Sockets / total 16 cores
16 CPU VPs
16 GB shared memory maximum
Unavailable Compression, Parallel Features,
Features Partitioning
Includes Advanced Access Control, Informix
features Warehousing Tool, HDR, RSS, CAF,
unlimited ER root & leafnodes
Partially HA and scalability: 1 primary, 2
available secondaries of any type (HDR, RSS,
features CAF/SDS cluster), updatable

14 © 2010 IBM Corporation

 Informix Ultimate Edition

• Create a failsafe, multi-site global availability plan while maximizing IT

investment
• Useful for large enterprises, ISVs for OEM use
• Includes all of the features of IDS Workgroup Edition plus features
required to provide the scalability to handle high user loads and
provide 24x7x365 high availability

Supported OS Linux, Unix, Windows, MacOS

Restrictions None
Features All features included, except Storage
Optimization Feature !
(i.e. compression is still an add-on feature)

15 © 2010 IBM Corporation

Informix New Packaging – 20 July 2010

• For more information go to

http://www.iiug.org/news/announcements/new_prices.php

• General Information:
http://www-01.ibm.com/software/data/informix/

• Developer works article comparing the different editions:

http://www.ibm.com/developerworks/data/library/techarticle/dm-
0801doe/

• For further questions email

askIFMX@us.ibm.com

© 2010 IBM Corporation

Agenda

• Introduction To Informix

• Informix Editions

• Informix Architecture Review

• Appendix

© 2010 IBM Corporation

 Informix Dynamic Scalable Architecture
Fan In for OLTP Fan Out for DSS/OLAP

Multi-Threading Parallel Database

Queries
Read ahead I/O
Integrated Extensibility
Async. I/O

Partitioned Data for VLDB Manageability

18 © 2010 IBM Corporation
 Informix Instance

• An Informix instance:
ONCONFIG
• Is a set of O/S processes and memory Parameter
Physical
file
that are shared by all databases defined Log

in that instance
OS
Data
• All server processes are multi-threaded Authentication
files
Databases Logical
Logs
• A single file for configuration parameters
Data
(called ONCONFIG) Backup Instance

• An Informix instance provides logical

access to the databases. It can contain SET ONCONFIG = onconfig.inst
more than one database, although SET INFORMIXSERVER=inst_tcp
oninit
most production instances have only a
single production database
• The terms "instance" and "database"
have distinct meanings and purposes

19 © 2010 IBM Corporation

ONCONFIG file
• Central configuration file
• Name can be set with environment variable $ONCONFIG

###################################################################
# System Configuration Parameters
###################################################################
# SERVERNUM - The unique ID for the IDS instance. Acceptable
# values are 0 through 255, inclusive.
# DBSERVERNAME - The name of the default database server
# DBSERVERALIASES - The list of up to 32 alternative dbservernames,
# separated by commas
###################################################################

SERVERNUM 1 # Unique id corresponding to a server instance

DBSERVERNAME demo_on # Name of default Dynamic Server
DBSERVERALIASES demo_on_drda # List of alternate dbserver names
….
© 2010 IBM Corporation
 Communications

• SQLHOSTS file
dbservername nettype hostname servicename options

demo_on onsoctcp localhost demo_on_svc b=8192, etc.

demo_on_drda drsoctcp 192.1.1.11 1523

• Examples of other communication types:

• ontlitcp
• onipcshm
• onipcstr
• drtlitcp
• drsoctcp

21 © 2010 IBM Corporation

 Typical Startup of an Informix instance (Unix/bash)

Required.
export INFORMIXSERVER=panther1 Use symbolic link to
simplify in-place upgrade.
export INFORMIXDIR=/opt/IBM/informix/
Required.
export PATH=$INFORMIXDIR/bin:$PATH Always in
$INFORMIXDIR/etc
export ONCONFIG=onconfig.$INFORMIXSERVER
export
INFORMIXSQLHOSTS=$INFORMIXDIR/etc/sqlhosts.$INFORMIXSERVER

oninit -v

© 2010 IBM Corporation

 Informix Engine Architecture – Overview

23 © 2010 IBM Corporation

 Virtual Processors

Every
Every process
process in
in the
the database
database server
server environment
environment
is
is known
known as
as aa virtual
virtual processor
processor (VP).*
(VP).*

These VPs:
Virtual processor class
• Runs threads of the
appropriate class
Virtual Virtual
• Belong to a VP class; processor processor
responsible for a specific set
of tasks (e.g. CPU, physical
log [PIO], logical log [LIO], Virtual Virtual
asynchronous I/O [AIO], processor processor
administrative functions
[ADM], auditing functions
[ADT], and more!
© 2010 IBM Corporation
Data Caching

• Data caching occurs when

pages of data stored on disk Shared memory buffer pool
are read into buffers in the
shared memory buffer pool:
• A process reads the data into
the shared memory buffers
• Subsequent page access is to
the shared memory copy of the Are read
page into
• Modifications are immediately
available to all server Pages
processes from disk
Chunk

© 2010 IBM Corporation

Physical Log

The
The physical
physical log
log is
is aa special
special log
log used
used for
for
automatic
automatic recovery
recovery purposes.
purposes.

Buffer pool
The physical log:
Before images
• is collection of contiguous
pages on disk
• is used to record before
images (first copy) of pages
that have been modified in
shared memory
Chunk Physical log

© 2010 IBM Corporation

 Logical Log

The
The logical
logical log
log is
is comprised
comprised of of logical
logical log
log files,
files, which
which are
are collections
collections of
of
contiguous
contiguous pages
pages used
used for
for transaction
transaction records,
records, andand physical
physical changes
changes in in
the
the underlying
underlying chunks
chunks to
to accommodate
accommodate these these changes.
changes.

The logical log: Chunk

• is used for logical recovery
of logged databases
Chunk
• Stores transaction records
(plus other information)
• is shared by all databases
in the server

© 2010 IBM Corporation

 Shared Memory Structures
Shared mem. header Buffer-header table LRU queues Resident Segment
Lock Table Physical-Log Buffer Logical-Log Buffer Control structures, logical and
Buffer Pool
physical log buffers, data buffer
cache.
Chunk Table Mirrored-Chunk Table
Dbspace Table Page Cleaner Table

TblespaceTable Transaction Table User Table Virtual Segment (Dynamic)

Memory pools and various
Session Structures Thread Structures Dictionary Cache
caches, thread information, Big
UDR Cache SQL Statement Cache Sorting Pool Buffers, session information

Thread Stacks Thread Heaps

Big Buffers

Global Pool

Message Segment
Unallocated Memory
Communication between local
clients using shared memory and
Client/server IPC messages
the database

28 © 2010 IBM Corporation

Page – The basic unit of storage and I/O in a server

• A page is the minimum I/O that is

read/written from/to disk
• The size of a page is preset
• 2K bytes on most UNIX systems,
4K on AIX and Windows
• Configurable page sizes introduced
in Informix 10
• Multiple of platform page size up to
16K
Page
• Pages have an internal structure
• There are different types of pages
• All database and system
information is stored on pages

© 2010 IBM Corporation

Extent – Collection of contiguous pages

• Allocation of contiguous pages on

disk
• Amount is specified for each table
when the table is created.
• Default initial Extent size and Next
Extent size = 16K for user defined
tables Physical
Storage
• Important to manage extent sizes
and number of extents
Extent
• Manage with EXTENT SIZE,
NEXT SIZE in CREATE and
ALTER TABLE statements

© 2010 IBM Corporation

Tblspace – Logical collection of Extents

• A tblspace is a collection of all the pages that

Tblspace
contain data or indexes for a database table
within a partition

• A tblspace is a logical collection of extents Extent

• An extent is a contiguous group of physical

pages

• Extents allocated to a tablespace are not Extent

necessarily contiguous

© 2010 IBM Corporation

Table – A Logical collection of tblspaces

• Can be tblspaces or can

be specialized tblspaces
called index spaces
Table

• Information about
tblepaces is stored in the
sysfragments system tblspace tblspace
catalog table
• Can be also retrieved with
oncheck –p[tT]
command tblspace
• Two tblspaces in the tblspace
same table, stored in the
same dbspace are called
partitions

© 2010 IBM Corporation

 Chunk – Physical storage unit

• Largest unit of physical disk

dedicated to database server data
storage
File Device
• A chunk can be a raw device
(character-special device), a piece /dev

of a raw device, or a UNIX file

(cooked file).
• Raw disk chunks have traditionally
performed better due to less OS
overhead, but not as noticeable
today with the use of Direct I/O
• Maximum size in Informix 10 and Cooked Raw
higher is 4TB
• The number of allowable chunks is
32,767
© 2010 IBM Corporation
 Dbspace – Logical collection of Chunks

• A dbspace is a logical collection of one Dbspace

or more chunks
Chunk 3
Chunk 1
• Can have between 1 and 32,767 chunks (File)
(Raw Device)

• Contain databases, tables, indexes,

logical logs and physical log, temporary Chunk 2
space (Raw
Device)

Dbspace Types Dbspace Storage Settings

• Regular dbspace • PAGESIZE
• Temporary dbspace • PATH (applies to first chunk in dbspace)
• Simple blobspace • OFFSET (applies to first chunk in dbspace)
• SmartBlob space • MIRROR (applies to first chunk in dbspace)
• External dbspace*
* An external dbspace is a mapping to a directory
34 © 2010 IBM Corporation
 Informix Physical Storage Model Overview

Dbspace

Chunk
Page
Chunk

Chunk

Extent

35 © 2010 IBM Corporation

Blobspace
• A blobspace is a special type of dbspace composed of one or more
chunks that store only simple large objects
• Simple Large Objects consist of TEXT or BYTE data types from any
columns or any tables (from any database)
• A blobpage is the basic unit of storage for blob data types stored in a
blobspace
• Configured to be a multiple of the system page size
• The database server writes data stored in a blobspace directly to disk
• This data does not pass through resident shared memory
Chunks
blobspace1 blobspace2

Raw Device Raw Device Raw Device Cooked File

© 2010 IBM Corporation

SBSpace
• An sbspace is a special type of dbspace composed of one or more
chunks that store smart large objects
• Smart large objects consist of a data type of BLOB (binary large object),
CLOB (character large object), or a user-defined data type (UDT)
• Sbspaces contains header data, metadata, and user data
• Smart large object pages have the same size and format as standard
system data pages
• The default is “buffering=ON”, which means to use the buffer pool
• If buffering is turned on, the database server uses private buffers in
the virtual portion of shared memory
Chunks
sbspace1 sbspace2

Raw Device Raw Device Raw Device Cooked File

© 2010 IBM Corporation

 Database Administration – System Catalog Tables

• Catalog tables are created in every Informix database

• Created automatically at database creation
• Table names begin with the letters SYS (e.g. sysindexes)
• Track and maintain information about the database,
including:
• Tables, views, and synonyms Getting all tables for a user:
• Columns, constraints, indexes, and fragments SELECT tabname FROM systables WHERE
• Triggers tabtype="T" and owner=user
• Data types and casts
• Aggregate functions
Getting all system tables:
• Access methods and operator classes
SELECT tabname FROM systables WHERE
• Sequence objects
tabid < 100
• External optimizer directives
• Inheritance relationships
• Authorized users, roles, and privileges to access database objects
• Procedures, functions, routines, and associated messages

38 © 2010 IBM Corporation

 Partitioned Tables (Fragmentation)

CREATE TABLE table1(col1 INT)

Fragmentation is a feature that FRAGMENT BY ROUND ROBIN
allows you to control where data is IN dbspace1, dbspace2, dbspace3
stored at the table level
CREATE TABLE table1(col1 INT)
Tables and indexes can be FRAGMENT BY EXPRESSION
fragmented across different (col1between 1 and 20) in dbspace1,
(col1 between 21 and 40) in dbspace2.
dbspaces or named partitions (col1 between 41 and 60) in dbspace3
within a dbspace
Transparent to the user and client CREATE TABLE cust (cid INT, …, state CHAR(2))
applications FRAGMENT BY LIST (state)
PARTITION p0 VALUES (‘KS’, ‘IL’) IN dbs0
Flexible DDL options PARTITION p1 VALUES (‘CA’, ‘NY’) IN dbs1,
PARTITION p2 REMAINDER IN dbs2;
Easy maintenance
Roll-in and roll-out ability
CREATE TABLE prod (pid INT, prod_price(10,2)
Can partition index as well with table or FRAGMENT BY RANGE (ROUND (prod_price))
have separate partition strategy INTERVAL (200) STORE IN (dbs1, dbs2, dbs3)
PARTITION p0 VALUES < 100 IN dbs0;

39 © 2010 IBM Corporation

Operation Modes

Operating Mode Status

OnLine Engine is running in multi-user
mode
Quiescent Engine is running in quiescent
mode (no sql allowed)
Recovery Engine is in fast recovery

Shutdown Engine is coming offline

Single User Engine is in single user (or

maintanance mode)

© 2010 IBM Corporation

 Checkpoints

A
A checkpoint
checkpoint is
is aa system
system event
event during
during which
which all
all
modified
modified buffers
buffers are
are copied
copied to
to disk
disk

1. No threads are allowed to enter

a critical section Shared memory buffer pool
2. Physical log buffers are flushed
to disk
3. Modified buffer pages are
flushed to disk
4. Checkpoint record is written to
reserved page
5. Logical log buffer is flushed to
disk
6. The physical log is logically Chunk
emptied

© 2010 IBM Corporation

 What Triggers Checkpoints?

• There are two types of checkpoints:

• Blocking checkpoint - the database server is blocked while buffer
pages are flushed to disk
• Blocking checkpoints are triggered:
• When administrative tasks are performed, such as adding a dbspace
• When a dbspace backup is performed
• When a checkpoint is manually requested by onmode –c

• Non-blocking checkpoint - the server is not blocked during page

flushing
• Non-blocking checkpoints are triggered:
• When resource limitations occur, such as when physical log is 75% full
• When the time interval indicated by the CKPTINTVL parameter has elapsed
• At server startup

© 2010 IBM Corporation

Fast Recovery

• Occurs during initialization to ensure consistency

• Two Phases
1. Physical Recovery
1. Before images in the physical log are restored to disk

2. Logical Recovery
1. Locate last checkpoint in logical log
2. Logical log records are rolled forward, replaying all
transactions
3. Uncommitted transactions are rolled back

© 2010 IBM Corporation

Mirroring

Mirroring
Mirroring is
is aa process
process ofof automatically
automatically writing
writing
identical
identical pages
pages to
to two
two different
different devices
devices

• Mirroring is decided at the

dbspace or blobspace level Shared Memory

• Must be enabled when the

server is booted
• Allows disk recovery without
user interruption
• Uses asynchronous but
parallel writes and alternate
Primary Chunk Mirror Chunk
chunk reads to enhance
performance

© 2010 IBM Corporation

 Parallel Data Query (PDQ)

Problem: Idle CPUs Solution: PDQ

USER USER

Task Task

IDLE IDLE Subtask Subtask

Subtask

CPU CPU CPU

100% transparent to
applications CPU CPU
CPU

45 © 2010 IBM Corporation

46 © 2010 IBM Corporation
Agenda

• Introduction To Informix Dynamic Server (IDS)

• Informix Editions

• Informix Tools and Drivers

• Informix Architecture Review

• Appendix

© 2010 IBM Corporation

 Announcement letters new packaging 20 July 2010

• New Informix Choice Edition announcement

• US: 210-272
• http://www-01.ibm.com/common/ssi/cgi-bin/ssialias?subtype=ca&infotype=an&supplier=897&letternum=ENUS210-272

• AP: AP10-0249
• http://www-01.ibm.com/common/ssi/cgi-bin/ssialias?subtype=ca&infotype=an&supplier=872&letternum=ENUSAP10-0249

• CAN: A10-0710
• http://www-01.ibm.com/common/ssi/cgi-bin/ssialias?subtype=ca&infotype=an&supplier=649&letternum=ENUSA10-0710

• EMEA: ZP10-0348
• http://www-01.ibm.com/common/ssi/cgi-bin/ssialias?subtype=ca&infotype=an&supplier=877&letternum=ENUSZP10-0348

• LA: LP10-0362
• http://www-01.ibm.com/common/ssi/cgi-bin/ssialias?subtype=ca&infotype=an&supplier=899&letternum=ENUSLP10-0362

© 2010 IBM Corporation

Announcement letters new packaging 20 July 2010

• Withdrawal of Ultimate-C Edition announcement

• US: 910-180
• http://www-01.ibm.com/common/ssi/cgi-bin/ssialias?subtype=ca&infotype=an&supplier=897&letternum=ENUS910-180

• AP: WP10-0086
• http://www-01.ibm.com/common/ssi/cgi-bin/ssialias?subtype=ca&infotype=an&supplier=872&letternum=ENUSWP10-0086

• CAN: A10-0672
• http://www-01.ibm.com/common/ssi/cgi-bin/ssialias?subtype=ca&infotype=an&supplier=649&letternum=ENUSA10-0672

• EMEA: ZP10-0327
• http://www-01.ibm.com/common/ssi/cgi-bin/ssialias?subtype=ca&infotype=an&supplier=877&letternum=ENUSZP10-0327

• LA: LP10-0339
• http://www-01.ibm.com/common/ssi/cgi-bin/ssialias?subtype=ca&infotype=an&supplier=899&letternum=ENUSLP10-0339

© 2010 IBM Corporation

 Informix Express Edition
• Self-managing data server for mid-market businesses
• Provides the power, function and reliability with simplicity in
packaging, installation and deployment at a minimal investment
cost
Supported OS Linux, Windows (both 32bit only)

Restrictions 200 PVUs

4 CPU VPs
4 GB shared memory maximum
Unavailable Compression, Parallel Features,
Features Partitioning, Advanced Access Control,
Informix Warehousing Tool, HDR, RSS,
CAF, ...
Included CLR, VTI, free datablades, deployment
features wizard
50 © 2010 IBM Corporation
Connections Supported by Informix

Connection Windows UNIX® Local Network

Type
Sockets X X X X

TLI (TCP/IP)) X X X

TLI (IPX/SPX) X X X

Shared X X
memory
Stream pipe X X

Named pipe X X

© 2010 IBM Corporation

 Virtual Processor Classes
VP Class Category Purpose
CPU Central Runs all session threads and some system threads. Runs thread for kernel asynchronous I/O (KAIO)
processing where available. Can run a single poll thread, depending on configuration.
PIO Disk I/O Writes to the physical-log file (internal class) if it is in cooked disk space.
LIO Disk I/O Writes to the logical-log files (internal class) if they are in cooked disk space.
AIO Disk I/O Performs non-logging disk I/O. If KAIO is used, AIO virtual processors perform I/O to cooked disk spaces.
SHM Network Performs shared memory communication.
TLI Network Uses the Transport Layer Interface (TLI) to perform network communication.
STR Network Uses operating systems stream-pipe to handle communications.
SOC Network Uses sockets to perform network communication.
OPT Optical Performs I/O to optical disk.
(UNIX®)
ADM Administrative Performs administrative functions.
ADT Auditing Performs auditing functions.
MSC Miscellaneous Services requests for system calls that require a very large stack.
CSM Communicatio Performs communications support service operations.
ns Support
Module
Encrypt Encryption Used by the database server when encryption or decryption functions are called.
classname User defined Runs user-defined routines in a thread-safe manner so that if the routine fails, the database server is
unaffected. Specified with the VPCLASS configuration parameter. You must specify classname.
Java VP Java UDR Executes Java UDRs. Contains the Java Virtual Machine (JVM).
(JVP)
© 2010 IBM Corporation
Summary of NETTYPE and Protocol Values
nettype Value Protocol Value Description Connection
(UNIX® or (Windows® ) Type
Linux®)
drsocssl drsocssl Secured Sockets Layer (SSL) protocol for DRDA® Network
drsoctcp drsoctcp Distributed Relational Database Architecture™ Network
(DRDA)
drtlitcp drtlitcp DRDA Network
onipcshm Shared-memory communication IPC
onipcstr Stream-pipe communication IPC
onipcnmp Named-pipe communication IPC
ontlitcp TLI with TCP/IP protocol Network
onsocssl onsocssl SSL protocol Network
onsoctcp onsoctcp Sockets with TCP/IP protocol Network
ontlispx TLI with IPX/SPX protocol Network
onsocimc Sockets with TCP/IP protocol for communication Network
with Informix MaxConnect
ontliimc TLI with TCP/IP protocol for communication with Network
Informix MaxConnect
onsqlmux onsqlmux Single network connection with multiple database Network
connections.

© 2010 IBM Corporation

Summary
New Editions - Creating Growth Opportunities for Clients
and Partners

Informix Innovator-C Edition Informix Choice Edition

• Ideal for small applications with HA • Breath of functionality
• Free to download, develop, and deploy • Ideal for med-size applications with HA
• All platforms • Windows and Mac OS X
•Support is optional Informix Growth Edition
• Requires IBM license to redistribute • Ideal for small to medium size business
• Rich HA options
Informix Developer Edition • Shared disk clustering
• Full functionality, limited scalability • All Platforms
• Free to download, develop, test Informix Ultimate Edition
• All platforms • Full enterprise class functionality
for all businesses
• Unlimited scalability
• All Platforms

© 2010 IBM Corporation

 List Fragmentation Strategy

• Fragments data based on a list of discrete values

• E.g. states in the country or departments in an organization
• Can have a remainder fragment

Fragment key
List values
expression

CREATE TABLE customer (cust_id INT, ..., state CHAR(2))

FRAGMENT BY LIST (state)
PARTITION p0 VALUES(‘KS‘, ‘IL‘) IN dbs0, list
PARTITION p1 VALUES(‘CA‘, ‘NY‘) IN dbs1, fragments
PARTITION p2 REMAINDER IN dbs2; remainder
fragment

© 2010 IBM Corporation

 Interval Fragmentation Strategy
• Fragments data based on a specified interval
• Automatically creates a new fragment if it does not exist yet
Would create fragements
< 100
>= 100 AND < 300
>= 300 AND < 500
Interval Fragment key …
value expression
If you need them (e.g. insert data)

CREATE TABLE product(prod_id INT, prod_price MONEY(10,2))

FRAGMENT BY RANGE (ROUND(prod_price))
INTERVAL(200) STORE IN (dbs1,dbs2, dbs3, dbs4)
PARTITION p0 VALUES < 100 IN dbs0; Range
fragement

Range expression
© 2010 IBM Corporation
 Informix Engine Architecture – High Level View

Shared memory segments allocated to

that instance. The allocation of these
segments can be dynamic for some
types of segments, or a pre-allocation of
memory.

One or more processes that do the tasks

requested by the instance. The allocation
* of these processes can be dynamic or
pre-allocated, depending on the type of
process.

Disk is a storage facility that contains

persistent instance structures to hold
data. The allocation of these structures
can be dynamic or pre-allocated,
depending on the type of disk allocation.
* other names: instance, online system, database server, engine.

57 © 2010 IBM Corporation

Mirroring

Writes: Split Reads:

• Parallel writes to both chunks • Data is alternately read from
primary and mirror chunk

Shared Memory Shared Memory

Primary Chunk Mirror Chunk Primary

Mirror
Chunk
Chunk

© 2010 IBM Corporation

 Informix – Rated #1 Two Years in a Row

IBM Informix is the database software voted number #1 in customer satisfaction.

Clients choose Informix because it is reliable, low cost, and hassle free.
Solution providers choose Informix for its best-of-breed embeddability.
Available on Windows, Linux, Unix, and MAC.

IBM Informix was named #1 in customer satisfaction among database software vendors in
2008 and 2009. Informix’ score of 94.3 out of a possible 100 was the top overall mark for
2009. Informix has placed first or second on VendorRate in each quarter since Q3 of 2008.
http://www.vendorrate.com/news/2009-12-15-VendorRate-Annual-Report.htm

© 2010 IBM Corporation

 IBM Data Servers
Lower the cost of data and improve service levels
solidDB: In-Memory Database
Informix: High Performance with less Software for Extreme Speed
resources Optimized to accelerate OLTP
Reliable, low cost, and hassle free. workloads up to 10X.
Best-of-breed embeddability.
Unique flexible Grid technology.
Smart metering with built-in handling
of time series data.
Administration free zone.
OLTP and analytical workloads. Database
Innovations

DB2: Lower Operational Costs

Optimized to lower administration,
storage, development, and server costs IMS: Highest Performance Application
while delivering top performance for and Database Software for System Z
OLTP and analytical workloads Optimized for transactional and hierarchical
OLTP workloads requiring highest levels of
performance, scalability, and reliability
60 © 2010 IBM Corporation
Informix 11.7 Bootcamp
Fundamentals
Information Management Technology Ecosystem

© 2010 IBM Corporation