How to go hand-in-hand with DB2 and Informix

Database technology is a constantly growing field of knowledge. Leveraging your current knowledge on one product and applying it to another similar product is one way to keep up with the constant change. This article demonstrates how you can leverage skills acquired in either Informix or DB2 to learn the other, and compares the technologies and terminologies used in IBM® Informix® Dynamic Server (IDS) 10 with IBM DB2® 9.

Share:

Suma C. Shastry (suma.chakrabarti@in.ibm.com), Staff Software Engineer, IBM

Suma C. ShastrySuma Shastry is a project lead working with IBM Software Labs, India for the Information Management team. She has six years of working experience in DB2. Her primary focus is DB2 tools development. She is a certified IBM DB2 DBA and has expertise in SVT, FVT, regression, and test automation.



Mohan Kumar (mohankumarsp@in.ibm.com), System Software Engineer, IBM

Photo: Mohan KumarMohan works as a DB2 application developer at IBM Software Labs, India. His primary focus is DB2 Samples Development and is certified as an IBM DB2 Advanced DBA, Application Developer, and DB2 Problem Determination Master. He also has working knowledge on SVT and FVT.



Prasad Srinivasachar (srprasad@in.ibm.com), Advisory Software Engineer, IBM

Photo: Prasad SrinivasacharPrasad is an advisory software engineer working on Informix Classics products for the IBM Informix Development Team at ISL. He has many years of extensive work experience in Informix products and handled roles of both database administrator and an application developer.



25 January 2007

Also available in Chinese

Introduction

DB2 9 and IDS 10 are strategically positioned to make database management easier and faster, and have many built-in features. Some of the common features include self-healing manageability, support for transparent "silent" installation, support for a wide array of development paradigms, minimizing disk space requirements, and range partitioning. Table 1 highlights the major features of these products. For more details on both products, refer to the Resources section.

Table 1. Major features
DB2 9IDS 10
pureXML and hybrid data services
DB2 9 embodies technology that provides pureXML services, which is not just for data server external interfaces, rather it extends to the very core of the DB2 engine. The XML and relational services in DB2 9 are tightly integrated, thereby offering the industry's first pureXML and relational hybrid data server.
Security enhancements
IBM IDS 10 provides significant advancements in database server security, encryption, authentication, and availability.
Data compression
Data row compression technology in DB2 9 uses a dictionary-based algorithm for compressing data records. That is, DB2 9 can compress rows in database tables by scanning tables for repetitive, duplicate data, and building dictionaries that assign short, numeric keys to those repetitive entries. Text data tends to compress well because of recurring strings as well as data with lots of repeating characters, or leading or trailing blanks.
Server usability enhancements
The server usability enhancements include features for ease of administration, scalability, and high availability.
Self-tuning memory
The self-tuning memory manager in DB2 uses intelligent control and feedback mechanisms to keep track of memory consumption and demand for the various shared resources in the database, and dynamically adapts their memory usage as needed.
Performance enhancements
The performance enhancements include improved query performance and recovery time. In addition to the topics discussed below, enhancements have been made to improve performance in the following areas:
  • XA transactions
  • Nested ANSI-compliant left-outer joins
  • Subqueries
  • Full-outer joins
Label Based Access Control (LBAC) security
LBAC is a security feature in DB2 that provides granular read and write access at the individual row and column level. DB2 9 provides a new DB2 security administrator role (SECADM) with specific security privileges. The SECADM user can be given security related privileges that even the system administrator (SYSADM) does not have.
SQL enhancements
The SQL enhancement improves database availability. Creating and dropping indexes without locking tables.
Table partitioning
Table partitioning (sometimes referred to as range partitioning) is a data organization scheme in which table data is divided across multiple storage objects, called data partitions, according to values in one or more table columns. These storage objects can be in different table spaces, in the same table space, or a combination of both.
Enterprise replication enhancements
The Enterprise replication enhancements ease administration, improve data integrity, and allow additional SQL operations.
Application development enhancement
Application development enhancements in DB2 9 include a new Developer Workbench, deeper integration with .NET environments, rich support for XML, new drivers and adapters for PHP and Ruby interfaces, and new application samples.
Backup and restore enhancements
The backup and restore enhancements improve performance and debugging.
-Storage enhancements
The storage enhancements improve ease of use. The long identifier with the High-Performance Loader helps with storage. The Informix interface for Tivoli Storage Manager helps with efficient data storage.
-Extensibility enhancements
The extensibility enhancements improve distributed transactions, obtaining information from trigger executions, and Java support.
-Installation enhancements
The installation enhancements improve usability.
-Interoperability enhancements
The interoperability enhancement improves communication between Informix and DB2 products.

Editions and platform support

DB2 9 and Informix IDS 10 deliver the right data management solutions for any business. Both of the products offer various editions packaged with features and functions to suit a wide variety of customer needs. Small and mid-sized companies may select Express Editions, whereas Workgroup and Enterprise Editions are suitable for large enterprises. Along with these editions, DB2 9 offers two more editions: Personal Edition and Developer Edition, and a no-charge version, DB2 Express-C. Table 2 describes the editions available in DB 9 and Informix IDS 10.

Table 2. Editions and platform support
DB2 9IDS 10
DB2 Express Edition 9 for Linux, UNIX, and Windows
DB2 Express 9 is a fully-functioning DB2 data server, which provides very attractive entry-level pricing for the Small and Medium Business (SMB) market. It comes with simplified packaging and is easy to transparently install within an application. While it is easy to upgrade to the other editions of DB2 9, DB2 Express 9 includes the same autonomic manageability features of the more scalable editions.
IDS Express Edition 10 for Linux, UNIX, and Windows

IDS Express Edition is suitable for mid-sized companies. It is a fully-functioning object relational database server. IDS Express Edition includes features such as, self healing manageability features and simplified installation. A near-zero administration supports a wide array of development paradigms. A minimal disk space requirement supports extensibility.

DB2 Workgroup Server Edition 9 for Linux, UNIX, and Windows
DB2 Workgroup 9 is the data server of choice for deployment in a departmental, workgroup, or medium-size business environment. It is offered at an attractive price point for medium-size installations, while providing a fully-functioning data server.
IDS Workgroup Edition 10 for Linux, UNIX and Windows
IDS Workgroup Edition is suitable for departments within large enterprises and mid-sized companies. This edition includes all features of IDS Express Edition. Additionally, it supports parallel data query, parallel backup and restore, high performance loader, and high availability data replication (which can be purchased as add-on).
DB2 Enterprise Server Edition (ESE) 9 for Linux, UNIX, and Windows
DB2 ESE 9 is designed to meet the data server needs of mid- to large-sized businesses. DB2 ESE 9 is an ideal foundation for building on-demand, enterprise-wide solutions such as:
  • Large data warehouses of multiple terabyte size
  • High-performing, 24x7 available, high-volume transaction processing business solutions
  • Web-based solutions
Additionally, DB2 ESE 9 offers connectivity, compatibility, and integration with other Enterprise DB2 and IDS data sources.
IDS Enterprise Edition 10 for Linux, UNIX, and Windows
IDS Enterprise Edition is designed to meet the requirements of large enterprises. It includes all of the features of IDS Workgroup Edition, plus features required to provide the scalability to handle high loads, and 24x7 availability. This edition includes the following features:
  • Enterprise data replication
  • High availability data replication
DB2 Personal Edition for Linux, UNIX, and Windows
DB2 Personal 9 is a single-user, fully-functioning relational database, with built-in replication. It is ideal for desktop- or laptop-based deployments. DB2 Personal 9 can be remotely managed, making it the perfect choice for deployment in occasionally connected or remote office implementations that don't require multi-user capability.
-
Database Enterprise Developer Edition
This edition offers a package for a single application developer to design, build, and prototype applications for deployment on any of the IBM Information Management client or server platforms. This comprehensive developer offering includes DB2 Workgroup 9 and DB2 Enterprise 9, IDS Enterprise Edition V10, Cloudscape V10.1, DB2 Connect Unlimited Edition for zSeries, and all the DB2 9 features, allowing customers to build solutions that utilize the latest data server technologies.
-
DB2 Express-C
DB2 Express-C is a version of DB2 Express Edition (DB2 Express) for the community. DB2 Express-C is a no-charge data server for use in development and deployment of applications including: XML, C/C++, Java, .NET, and PHP. DB2 Express-C can be run on up to two dual-core CPU servers, with up to 4 GB of memory, any storage system setup and with no restrictions on database size or any other artificial restrictions.
-

Architecture overview - DB2 9 ESE compared with IDS 10

In DB2, an instance provides an independent environment where databases can be created and applications can be run against them.

Because of these independent environments, two or more instances can have databases with the same name. In Figure 1, the database MYDB2 is associated with the instance DB2, and another database MYDB2 is associated with a different instance MYINST.

Instance related commands in DB2

db2icrt instance_name - Create an instance
db2idrop instance_name - Drop an instance
set db2instnace=instance_name - Set the current instance
db2start - Start the current instance
db2stop - Stop the current instance

Instances allow users to have separate, independent environments for production, test, and development purposes. A default instance can be created during the DB2 installation. In Windows the default instance is called DB2 and in Linux and UNIX it is called db2inst1. Also, an instance can be created using db2icrt command as well.

Each DB2 instance can have one or more databases. Each instance has one database manager configuration file. In addition, each database has its own database configuration file, catalog tables, logs, reserved buffer pool area, and table spaces. Table spaces can be regular, long (for LOB data), user temporary, and system temporary. Tuning parameters, resource management, and logging can differ for each database and can be controlled at the database level.

Figure 1. Architecture overview for DB2
Architecture overview for DB2
Environment Variables
INFORMIXDIR - Installation Location
ONCONFIG - Instance Configuration File
INFORMIXSERVER - Instance Name
INFORMIXSQLHOSTS - Name of a file
containing Instance host&port info

Instance related commands in Informix
Oninit -i - Create an instance
Oninit - To start the current instance
Onmode -k - To stop the current instance

Similar to DB2, the IDS instance provides an independent environment where databases can be created and applications can be run against them. Each instance has one default dbspace (rootdbspace), configuration file, three system catalog databases called SysMaster, SysUtils and SysUsers, logs, and buffer pools. Additionally, you can optionally create a tempdbspace at the time of instance creation. An instance can have more than one dbspaces. Dbspaces can be regular, temporary, blobspaces, sbspaces, and extspaces. Unlike DB2, IDS databases share logs, buffer pools, and temporary dbspaces at the instance level. In addition, each database has its own catalog tables and user tables. Figure 2 shows two instances of IDS.

The IDS instance can be instantiated by using the oninit -i command. The instance name is specified by the environment variable INFORMIXSERVER. Unlike DB2, there is no explicit command to drop an instance. However, you can change the configuration parameters like root path and server number, and then use the oninit -i command again. This command instantiates all the instance related environment. Therefore, you should very careful while using this command. Instance creation in IDS uses a set of environment variables and configuration parameters defined in a file pointed by the ONCONFIG environment variable. Hence, its important that all the relevant configuration parameters are set before issuing the oninit -i command. Configuration parameters can be defined using the Informix utility onmonitor or by using any editor. Some of the configuration parameters are mentioned below.

  • ROOTNAME: rootdbs: # Root dbspace name
  • ROOTPATH: /dev/online_root: # Path for the device containing root dbspace
  • SERVERNUM: 0: # Unique ID corresponding to a OnLine instance
  • DBSERVERNAME: - : # Name of default database server
  • LOGFILES: 6: # Number of logical log files
  • TAPEDEV: /dev/tapedev: # Tape device path
  • LTAPEDEV: /dev/tapedev: # Log tape device path
  • LOCKS: 2000: # Maximum number of locks
Figure 2. Architecture overview for IDS
Architecture overview for IDS

Process model

Knowledge of the DB2 process model can help you determine the nature of a problem, because it helps you to understand how the database manager and its associated components interact. UNIX-based environments use an architecture based on system processes. For example, the DB2 communications listeners are created as system processes. Intel operating systems, such as Windows, use an architecture based on threads to maximize performance.

Agents

An agent can be thought of as a worker that performs all database operations on behalf of an application. There are two main types of DB2 agents:

  • Coordinator agent (db2agent): This agent coordinates work on behalf of an application and communicates to other agents using inter-process communication (IPC) or remote communication protocols. All connection requests from client applications, whether they are local or remote, are allocated a corresponding coordinator agent.
  • Subagent (db2agntp): When the intra_parallel database manager configuration parameter is enabled, the coordinator agent distributes the database requests to subagents (db2agntp). These agents perform the requests for the application. Once the coordinator agent is created, it handles all database requests on behalf of its application by coordinating subagents (db2agent) that perform requests on the database.

When an agent or subagent completes its work, it becomes idle. When a subagent becomes idle, its name changes from db2agntp to db2agnta. Idle agents reside in an agent pool. These agents are available for requests from coordinator agents operating on behalf of client programs, or from subagents operating on behalf of existing coordinator agents. The number of available agents is dependent on the database manager configuration parameters maxagents and num_poolagents. Figure 3 shows the DB2 process model.

Figure 3. The DB2 process model (for a non-partitioned database)
Process model for DB2

Each of the circles in the above figure represent engine dispatchable units (EDUs), which are known as processes on Linux or UNIX platforms, and threads on Windows.

db2fmp is a fenced mode process. It is responsible for executing fenced stored procedures and user-defined functions outside the firewall. db2fmp is always a separate process, but may be multi-threaded depending on the types of routines it executes.

Some of the important threads or processes used by each database is listed below:

  • db2pclnr: For buffer pool page cleaners.
  • db2logmgr: For the log manager. Manages log files for a recoverable database.
  • db2loggr: For manipulating log files to handle transaction processing and recovery.
  • db2dlock: For deadlock detection.
  • db2taskd: For distribution of background database tasks. The tasks are executed by processes called db2taskp.

The system controller (db2sysc) must exist in order for the database server to function. Also, many other threads and processes may be started to carry out various tasks. Some of them are listed below. Refer to the DB2 Information Center, found in the Resources section, to know more about the DB2 processes.

  • db2resync: The resync agent that scans the global resync list.
  • db2gds: The global daemon spawner on UNIX-based systems that starts new processes.
  • db2wdog: The watchdog on UNIX-based systems that handles abnormal terminations.
  • db2pdbc: Handles parallel requests from remote nodes (used only in a partitioned database environment).
  • db2fmd: The fault monitor daemon.
  • db2disp: The client connection concentrator dispatcher.
Figure 4. IDS memory architecture and background processes
IDS memory architecture

IDS is made of three major components, process, memory, and disk. The process component is discussed now. The memory and disk components are discussed later in this article.

IDS is a multi-threaded database server. The multi-threaded architecture uses fewer processes to carry out database activities. One process can do the work for more than one application through the use of threads. Processes can be allocated dynamically for the database server as needed, hence the term Dynamic Server. IDS allows for increased scalability. This multi-threaded implementation can accommodate a higher number of transactions with fewer additional resources.

Virtual processors (VPs)

The oninit processes known as Virtual processors, make up the IDS. Each VP belongs to a VP class. A VP class is responsible for a specific set of tasks. The oninit processes are mapped to different VP classes used by the database server. Briefly each of the VP classes are described below:

  • CPU VP: Where most of the processing occurs. The purpose of this class is to execute all CPU intensive activities of the database server processes. The administrator can increase or decrease the number of CPU VPs as needed by the database server.
  • KAIO VP: Kernel Asynchronous I/O VP runs internal threads to perform I/O to raw devices.
  • AIO VP: Used to perform I/O to operating system file system files (also called cooked files).
  • LIO VP: Runs internal threads that write to the logical log on the disk.
  • PIO VP: Runs internal threads to write to the physical log on the disk.
  • SHM VP: Handles the task of polling for new connections when the application is using the shared memory method of communication.
  • SOC VP: Handles polling tasks for the TCP/IP Berkeley sockets method of communication.
  • TLI VP: Handles polling tasks for the Transport Library Interface (TLI) programming interface for the TCP/IP or Internetwork Packet Exchange (IPX)/Sequenced Packet Exchange (SPX) communication with the application.
  • MSC VP: Runs threads for the miscellaneous tasks.

Memory model

Figure 5. DB2 memory structure
DB2 memory structure

This article explains instance shared memory and database shared memory.

Instance memory model

There is one instance shared memory set per DB2 instance. Instance shared memory is allocated when the database manager is started (db2start), and freed when the database manager is stopped (db2stop). It is used for instance-level tasks such as monitoring, auditing, and inter-node communication. The following database manager configuration (dbm cfg) parameters control the limits to the instance shared memory and its individual memory pools:

  • Instance memory (instance_memory): This parameter specifies the amount of memory that should be reserved for instance management. This includes memory areas that describe the databases on the instance.
  • Monitor heap (mon_heap_sz): This parameter determines the amount of the memory, in pages, to allocate for database system monitor data. Memory is allocated from the monitor heap when you perform database monitoring activities such as taking a snapshot, turning on a monitor switch, resetting a monitor, or activating an event monitor.
  • Audit buffer size (audit_buf_sz): This parameter specifies the size of the buffer used when auditing the database.This is for the use of the db2audit facility.
  • Fast communication buffers (fcm_num_buffers): This parameter specifies the number of 4 KB buffers that are used for internal communications (messages) both among and within database servers, as well as inter-node communication between partitions and agents. Partitioned instances or instances with INTRA_PARALLEL set to ON.

Database memory model

The full green boxes in the figure below mean the memory pools are allocated in full when the database is started. Otherwise, only partial amounts of memory is allocated. For example, when a database is first started, only about 16 KB of memory is allocated to the utility heap, regardless of the value of util_heap_sz. When a database utility, such as backup, restore, export, import, and load, is started, then the full amount specified by util_heap_sz is allocated.

Figure 6. The DB2 database shared memory
The DB2 database shared memory

The database buffer pool(s) area is normally the largest component of the database shared memory. This is where all regular and index data is manipulated by DB2. A database must have at least one buffer pool, and can have a number of buffer pools depending on such things as the workload characteristics and database page sizes used in the database.

Theshared memory component of IDS is at instance level and is divided into three segments, as shown in the Figure 4 above.

  • Resident memory segment: This segment, known also as the resident portion, contains the buffer pool used to cache pages from the database, least-recently used (LRU) queues, logical log buffers, physical log buffers, and contains numerous structures to track resources used by the server. This helps in faster access.
  • Virtual memory segment: This virtual portion is used for maintaining and controlling the resources needed by processes. This segment contains information about the threads and sessions, and the data that is used by them. This information grows and shrinks constantly. The database server manages the allocation and de-allocation of memory in this portion. Virtual segment also contains dictionary cache, stored procedures cache, and big buffer pools used for writing large block of pages to disk at once. This portion also grows when sorting data, for example when building big indexes by the server.
  • Communications segment: The shared memory communications portion, also known as the message portion, is used as a communication mechanism by the client and server processes. This portion holds the message buffers that are used in communication between the client and the server, and when the communication method is through shared memory.

Disk component is a collection of one or more units of disk space assigned to the database server. All the system information to maintain the server system, and all databases data are stored within the disk component. IDS can have from one to 256 instances on a single computer, as can DB2.

Figure 7. High-level IDS instance architecture
High-level IDS instance architecture

DB2 breaks and manages memory in four different memory sets. They are as follows:

  • Instance shared memory
  • Database shared memory
  • Application group shared memory
  • Agent private memory

Each memory set consists of various memory pools (also referred to as heaps). The names of the memory pools are also given in Figure 6. For example, the lock list is a memory pool that belongs to the database shared memory set. The sort heap is a memory pool that belongs to the agent private memory set.


Database creation and storage model

In DB2, the database can be created using the database creation command or using the control center tool. This article deals with how to create the database and database objects using commands. Before exploring the commands, see what table spaces are.

Table spaces

A table space is a storage structure containing tables, indexes, large objects, and long data. Table spaces reside in the database. They allow you to assign the location of the database and table data directly onto containers. (A container can be a directory name, a device name, or a file name.) This can provide improved performance and more flexible configuration. A database can have more than one table space, where as a table space cannot belong to more than one database.

Table space management

In DB2, table spaces are managed in two different ways:

  • System managed space (SMS): SMS table spaces are managed by the operating system. Containers are defined as regular operating system files and they are accessed through operating system calls. This means that all the regular operating system functions handle the following: I/O is buffered by the operating system, space is allocated according to the operating system conventions, and the table space is automatically extended when necessary. However, containers cannot be dropped from SMS table spaces, and adding new ones is restricted to partitioned databases. The three default table spaces explained in the previous section are SMSs.
  • Database managed space (DMS): DMS table spaces are managed by DB2. Containers can be defined either as files (which are fully allocated with the size given when the table space is created) or devices. DB2 manages as much of the I/O as the allocation method and the operating system allows. Extending the containers is possible by using the ALTER TABLESPACE command. Unused portions of DMS containers can also be released (starting with Version 8). When you create a database, three table spaces are created (SYSCATSPACE, TEMPSPACE1, and USERSPACE1).

Table spaces are classified on their usage and manageability. There are five different table spaces by usage:

  • Catalog table space: There is only one catalog table space per database, and it is created when the CREATE DATABASE command is issued. Named SYSCATSPACE by DB2, the catalog table space holds the system catalog tables. This table space is always created when the database is created.
  • Regular table spaces: Regular table spaces hold table data and indexes. It can also hold long data, such as large objects (LOBs), unless they are explicitly stored in long table spaces. A table and its indexes can be segregated into separate regular table spaces, if the table spaces are DMS. The differences between DMS and SMS is defined later in this article. At least one regular table space must exist for each database. The default is named USERSPACE1 when the database is created.
  • Long table spaces: Long table spaces are used to store long or LOB table columns and must reside in DMS table spaces. They can also store structured type columns or index data. If no long table space is defined, then LOBs are stored in regular table spaces. Long table spaces are optional and none are created by default.
  • System temporary table spaces: System temporary table spaces are used to store internal temporary data required during SQL operations such as sorting, reorganizing tables, creating indexes, and joining tables. At least one must exist per database. The default created with the database is named TEMPSPACE1.
  • User temporary table spaces: User temporary table spaces store declared global temporary tables. No user temporary table spaces exist when a database is created. At least one user temporary table space should be created to allow the definition of declared temporary tables. User temporary table spaces are optional and none are created by default.

Figure 8 shows the database, which has five table spaces: a catalog, two regular, a long, and a system temporary table space. No user temporary table space was created. There are eight containers.

Figure 8. DB2 Database with table spaces and buffer pools
DB2 Database with table spaces and buffer pools

The following commands can be used to create such a database and table spaces.

create database sample
connect to sample
create bufferpool BP1 size 1000 pagesize 4 K
create bufferpool BP2 size 1000 pagesize 8 K
create bufferpool BP3 size 1000 pagesize 32 K

create regular tablespace userspace1 pagesize 8 k managed by database
using (file 'C1U1' 1000, file 'C2U1' 1000) bufferpool BP2 

create regular tablespace userspace2 pagesize 4 k managed by database
using (file 'C1U2' 1000) bufferpool BP1 

create large tablespace largespace1 pagesize 32 k managed by database
using (file 'C1L1' 1000, file 'C2L1' 1000, file 'C3L1' 1000) bufferpool BP3

create temporary tablespace systemp1 pagesize 32 k managed by system 
using (path '/db1/C1T1') bufferpool BP3

In the above set of commands, buffer pools and table spaces have been created. Now see what containers and buffer pools are in DB2.

Containers

Every table space has one or more containers. Again, you might think of a container as being a child, and a table space as its parent. Each container can only belong to a single table space, but a table space can have many containers. Containers can be added to, or dropped from, a DMS table space, and their sizes can be modified. Containers can only be added to SMS table spaces on partitioned databases in a partition, which does not yet have a container allocated for the table space. When new containers are added, an automatic rebalancing starts to distribute the data across all containers. To know more about containers and rebalancing, refer to the DB2 9 Information Center.

Buffer pools

A buffer pool is associated with a single database, and can be used by more than one table space. When considering a buffer pool for one or more table spaces, you must ensure that the table space page size and the buffer pool page size are the same for all table spaces that the buffer pool services. A table space can only use one buffer pool. When the database is created, a default buffer pool named IBMDEFAULTBP is created, which is shared by all table spaces. More buffer pools can be added by using the CREATE BUFFERPOOL statement. Large buffer pools also have an effect on query optimization, since more of the work can be done in memory. To know more about bufferpools, refer to the DB2 9 Information Center.

Database creation in IDS

Now that you understand how databases are created and table spaces are managed in DB2, see how dbspaces and databases are related to each other in IDS.

dbspaces

A dbspace is a logical unit made up of one or more chunks. Chunks represent physical units of storage. The database server can use cooked files or raw devices to store data. A database is created in a regular dbspace, hence a regular dbspace must exist before creating a database. A default regular dbspace called rootdbs is created during the IDS instance creation. This dbspace is used while creating a database, unless a separate regular dbspace is mentioned in the create database command. System catalog tables reside in the same dbspace as the database itself. All the dbspaces can be used by any other databases of the same Informix instance. IDS has a variety of dbspaces, which are briefly described below.

  • Regular dbspaces: Regular dbspaces hold database objects like system catalog tables, user tables, and indices.
  • Temporary dbspaces:: A temporary dbspace is a regular dbspace, used by the database server to store temporary tables. The database server does not perform logging when using temporary dbspaces. This improves performance, as less I/O and checkpoints occur.
  • Blobspaces: A blobspace is a logical unit consisting of one or more chunks. Blobspaces are used to store text and byte data. The database server writes data stored in a blobspace directly to disk. Blobspace objects are not logged.
  • Sbspaces: An sbspace is a logical unit consisting of one or more chunks. Sbspaces are used to store smart large objects. Smart LOBs consist of character large objects (CLOBs) and binary large objects (BLOBs) data types. Database objects stored in sbspaces can be logged. Using Informix APIs, an application can store or retrieve parts of smart LOBs stored in sbspaces.
  • Extspaces: An extspace is a logical name associated with a arbitrary string that signifies the location of external data. Extspaces are used when datablades (to extend the functionality of IDS) are developed. Contents of extspaces are accessed by using the corresponding user-defined access method.

For example if you run the data definition languages (DDLs) mentioned below, dbspace Dbdbspace of size 2 GB would be created first, and then database sampledb is created in the dbspace Dbdbspace. System catalog tables for sampledb reside in Dbdbspace.

Onspaces -c   -d  Dbdbspace -p /work/database/chunk1 -o 0 -s 20480000 

where,
	c  For Creating Dbspace
	p  path name for the physical unit
	o  offset in K bytes
	s  size of dbspace in Kbytes
	a  for adding chunk to a dbspace

Create database sampledb in Dbdbspace;
Figure 9. IDS Database with Dbspaces and chunks
IDS database with dbspaces and chunks

As shown in Figure 9, all database objects for a database can be stored in number of dbspaces. In this example, there is a default Root Dbspace, three regular dbspaces called Root Dbspace, User Dbspace and Index Dbspace, one Temporary Dbspace, one Blobspace, one smart LOB sbspace, and one extspace. In the example above, database objects of Database 1 spawn across all the available dbspaces. Tables and indexes can reside in different dbspaces. For better understanding , a regular dbspace is named as Index Dbspace, and you can opt for creating indices in this dbspace. You also have the option of creating the database in the root dbspace, although it is not considered a best practice. In Figure 9, Database 2 resides in the Root Dbspace.

The following adds a chunk to the userDbSpace: Onspaces -a -d UserdbSpace -p /work/database/chunk3 -o 0 -s 4096000 . For more information on dbspaces and adding chunks to dbspaces, refer to the IDS v10.0 Information Center.


Backup and recovery

Backup in DB2 is a database copy, together with control information, ready to be restored in the event of a failure. A database backup minimizes data loss and gives you the ability to reconstruct the failed database from the backup copy using the recovery process. A backup in IDS is a copy of one or more dbspaces, blobspaces, spspaces, and logical logs and physical logs of an Informix instance.

Database backup is taken in DB2 by using the BACKUP command:

BACKUP DATABASE sample ONLINE TO /dev/rdir1, /dev/rdir2

In IDS, there are two utilities named ontape and onbar.

The ontape utility is the older version of the Informix backup and restore utility designed to be used with up to two locally connected backup devices, one for instance backup and the other for logical log backups. Starting with IDS v10, ontape operations can now be directed to or from "standard in" or "standard out" (STDIO), thereby providing support for a wider range of options.

The onbar backup utility has two components: The onbar API and the storage manager. Onbar API is the Informix implementation of the client component of Open Systems Backup Services Data Movement (XBSA) API defined by the X/Open Organization.

The ontape utility backs up the dbspaces sequentially, where as onbar can backup dbspaces in parallel.

Figure 10. Backup utility in IDS
Backup utility in IDS

Types of logs

  • Active logs: A log is considered active if either of the following two conditions are satisfied:
    • It contains information about transactions that have not yet been committed or rolled back
    • It contains information about transactions that have committed, but whose changes have not yet been written to the database disk (externalized).
  • Online archive logs: These logs contain information for committed and externalized transactions. Such logs are kept in the same directory as the active logs.
  • Offline archive logs: Archive logs that have been moved from the active log directory to another directory or media. This move can be done either manually or automatically.

Apart from these, IDS categorizes logs as logical and physical. The number of log files can be defined by LOGPRIMARY and LOGSECONDARY database configuration parameters in DB2. In IDS, this is done by setting the configuration parameter LOGFILES. Similar to LOGSECONDARY in DB2, IDS provides dynamic logging option. This is can be enabled by setting the configuration parameter DYNAMIC_LOGS. To learn more about log types, refer to the information centers for DB2 and IDS found in the

Resources

section.

Logging mechanisms

IDS and DB2 have similar kinds of logging mechanism available. Both types are briefly described below:

  • Circular logging: Circular logging is the default logging mode for DB2. As the name implies, this type of logging reuses the logs in a circular mode. For example, if you had four primary logs, they would be used in this order: Log #1, Log #2, Log #3, Log #4, Log #1, Log #2, and so on. A log can be reused in circular logging as long as it only contains information about transactions that have already been committed and externalized to the database disk. In other words, if the log is still an active log, it cannot be reused. In IDS, logical files are always used in circular fashion. However, you can backup these logical log files for restore purposes. The logical files can be backed up to the path mentioned in the LTAPEDEV configuration parameter.
  • Archival logging: When you use archival logging, you are archiving (retaining) the logs. While in circular logging you overwrite transactions that were committed and externalized, with archival logging you keep them. For example, if you had four primary logs, they might be used in this order: Log #1, Log #2, Log #3, Log #4, (archive Log #1 if all its transactions are committed and externalized), Log #5, (archive Log #2 if all its transactions are committed and externalized), Log #6, and so on. Archive logging can be turned on by setting the database configuration parameter LOGRETAIN to ON. IDS archives the log files incase, the configuration parameter LTAPEDEV points to an valid path or device. If the parameter is pointing to null, then the logs are not archived.

Backup mechanisms

Now understand different types of backup mechanisms available in DB2 and IDS.

  • Offline backup: Offline backup is the simplest form of backup in DB2 9 as well as in IDS. In the case of an offline backup, full database backup is taken while the database is put offline. In other words, users are not allowed to access the database during an offline backup. In the case of IDS, the database server is changed to Quiescent mode. In this mode, users are not able to access the database server. Examples for offline backup in DB2 and backup in Quiescent mode for IDS are given below:
    In DB2(windows)
    backup database sample to c:\backup
    
    In IDS 
    ontape -s -L 0 (Level 0 backup)
    onbar -b -L 0
  • Online backup: Online backup can be taken even when the applications are connected to the database. In order to take an online backup, archive logging must be turned on in the case of DB2. IDS does not restrict you from taking online backup even in the case of circular logging. Besides database backups, you can take a table space level backup in the case of DB2, and a dbspace backup in the case of IDS. The full back up of the database is called a level 0 backup in IDS, and complete backup in DB2. The complete backup in DB2 should be an offline backup. In IDS, a level 0 backup can be taken even online.

    In DB2 
    backup database sample tablespace( syscatspace, userspace1,
    userspace2 ) online to /db2tbsp/backup1, \ /db2tbsp/backup2 In IDs onbar -b rootdbs, userdbs1, userdbs5
  • Incremental backup: Both DB2 and IDS supports incremental backup. Incremental backup is a backup of all of the data that has changed since the last full database backup. In IDS, an incremental backup is called a level 1 backup.
    Figure 11. Incremental backup
    Incremental backup
    In DB2
    (Sun) backup db mydb from c:\backup
    (Mon) backup db mydb online incremental from c:\backup
    (Tue) backup db mydb online incremental from c:\backup
    
    In IDS 
    ontape -s -L 1 (Level 1 backup)
    onbar -b -L 1
  • Delta backup: Delta backup is backup of only the data that has changed since the last successful full, incremental, or delta backup. A delta backup is called a level 2 backup.
    Figure 12. Delta backup
    Delta backup
    In DB2
    (Sun) backup db mydb from c:\backup
    (Mon) backup db mydb online incremental delta from c:\backup
    (Tue) backup db mydb online incremental delta from c:\backup
    
    In IDS 
    ontape -s -L 2 (Level 2 backup)
    onbar -b -L 2

Database recovery

Recovery of the database can be done using the restore utility in DB2, and ontape or onbar utilities with the -r option in IDS. The restore utility uses a backup file as an input and a new or existing database as the output. You can recover either the complete database as well as table space. in the case of DB2, and dbspaces in the case of IDS. In IDS, you can restore the dbspaces, physical logs, and logical logs. To omit logical files being restored from the backup, the -p option needs to be used. To restore only the logical files, the -l option can be used. Both IDS and DB2 allow incremental recovery.

Examples of complete, incremental, table space level, and dbspace level recovery are given below.

Complete recovery
In DB2
restore database sample from c:\backup taken at 20060314131259 
without rolling forward without prompting

In IDS
ontape -r  
onbar -r
Incremental recovery
In DB2
restore database mydb incremental taken at 20060414131259

In IDS 
In case of ontape, it would prompt the user to insert any 
incremental or delta backups to be restored.
Table space and dbspace level recovery
In DB2
restore database sample tablespace( mytblspace1 ) online from 
/db2tbsp/backup1, /db2tbsp/backup2

In IDS
Onbar  -r   userdbs1,  userdbs5

Database rollforward

The rollforward command allows for point-in-time recovery. This means that the command lets you traverse the DB2 logs and redo or undo the operations recorded in the log up to a specified point in time. In IDS, you can achieve this by restoring only the dbspaces, physical logs with the -p option, followed by a restore with the -l option. Note that these need to be done sequentially. In the case of the onbar utility, you can rollforward to a point in time or up to any specified log. In IDS, the onbar utility supports a point-in-time restore and point-in-log restore.

In DB2

rollforward  database sample to end of logs and complete
rollforward  database sample to timestamp and complete 
rollforward  database sample to timestamp using local time and complete 

In IDS
onbar   -r    -t     time  
onbar   -r    -l     logid

Security features

Both the DB2 and IDS security model consist of two main components: authentication and authorization.

Figure 13. The DB2 security model
The DB2 security model

DB2 authentication

Authentication is the process of validating a supplied user ID and password using a security mechanism. User and group authentication is managed in a facility external to DB2, such as the operating system, a domain controller, or a Kerberos security system. This is different from other database management systems (DBMSs), such as Oracle and SQL Server, where user accounts may be defined and authenticated in the database itself as well as in an external facility, such as the operating system. If the user credentials are not provided while connecting, DB2 implicitly uses the user ID and password that were used to log in to the workstation where the request originated.

By default, the instance is set up to use one type of authentication for all instance-level and connection-level requests. This is specified by the database manager configuration parameter AUTHENTICATION. Introduced in Version 9, is the database manager configuration parameter SRVCON_AUTH. This parameter specifically deals with connections to databases. So, for example, if you have the following set in your DBM CFG:

DB2 GET DBM CFG
Server Connection Authentication (SRVCON_AUTH) = KERBEROS
Database manager authentication (AUTHENTICATION) = SERVER_ENCRYPT

Then attachments to the instance would use SERVER_ENCRYPT. However, connections to the database would use KERBEROS authentication. If KERBEROS was not properly initialized for the server, but a valid user ID and password was supplied, then the user would be allowed to attach to the instance, but not allowed to connect to the database.

The following table summarizes the available DB2 authentication types. In a client-gateway-host environment, these authentication options are set on the client and gateway, not on the host machine.

Table 3. Authentication types in DB2
TypeDescription
SERVERAuthentication takes place on the server.
SERVER_ENCRYPTAuthentication takes place on the server. Passwords are encrypted at the client machine before being sent to the server.
CLIENTAuthentication takes place on the client machine.
*KERBEROSAuthentication is performed by the Kerberos security software.
*KRB_SERVER_ENCRYPTAuthentication is performed by Kerberos security software if the client setting is KERBEROS. Otherwise, SERVER_ENCRYPT is used.
DATA_ENCRYPTAuthentication takes place on the server. The server accepts encrypted user IDs and passwords, and encrypts the data. This operates the same way as SERVER_ENCRYPT, except the data is encrypted as well.
DATA_ENCRYPT_CMPAuthentication is the same as for DATA_ENCRYPT, except that this scheme allows older clients that don't support the DATA_ENCRYPT scheme to connect using the SERVER_ENCRYPT authentication. The data in this case is not encrypted. If the client connecting supports DATA_ENCRYPT, it is forced to encrypt the data, and cannot downgrade to the SERVER_ENCRYPT authentication. This authentication type is only valid in the server's database manager configuration file, and is not valid when used on the CATALOG DATABASE command on a client or gateway instance.
GSSPLUGINAuthentication is controlled by an external GSS-API plugin.
GSS_SERVER_ENCRYPTAuthentication is controlled by an external GSS-API plugin. In the case where the client doesn't support one of the server's GSS-API plugins, SERVER_ENCRYPT authentication is used.

*These settings are valid only for Windows 2000, AIX, Solaris, and Linux operating systems.

IDS security mechanisms

Figure 14. The IDS security model
The IDS security model

IDS authentication

In IDS, there are four options for authentication depicted in Figure 14. The following is a brief description of each of them:

Table 4. IDS security mechanisms
MethodAttributesDescription
OS user ID No Encryption, uses OS password lookup IDS has always used this basic authentication. This technique uses an OS user ID and password for each user who connects to the DBMS. The user ID and password are submitted by the user or application program, and the DBMS verifies the password using an OS library function. If the OS function indicates the user ID or password (or both) are not in the OS set of user IDs and passwords, then the DBMS connection is rejected.
Password encryptionOS user ID but with the password encrypted during transmission.IDS supports passwords to be encrypted when the password is sent from the application to the database server. This is accomplished by configuring password encryption by both the client and server in their respective SQLHOSTS files or registries and the conscm.cfg file.
Pluggable Authentication Model (PAM) User-provided authentication methodsAuthentication using PAM enables you to write your own methods to authenticate a user. PAMs can also be chosen among modules available from third parties. Library files making up the PAM usually reside in $INFORMIXDIR/lib and the configuration files of PAM are located in $INFORMIXDIR/etc. Both are referenced in the concsm.cfg file.
Lightweight Directory Access Protocol (LDAP)User-provided access to the LDAP directoryLDAP enables you to administer user accounts at a central place, which is the LDAP server. Using LDAP, there is no longer a need for creating users on the database server machine.

Authorities and privileges

Authorization is the process of determining access and privilege information about specific database objects and actions for a supplied user ID. DB2 stores and maintains user and group authorization information internally. Each time you submit a command, DB2 performs authorization checking to ensure that you have the correct set of privileges to perform that action.

DB2 uses five different levels of authority to control how users perform administrative or maintenance operations against an instance or a database. These five levels are:

  • System Administrator (SYSADM) authority
  • System Control (SYSCTRL) authority
  • System Control (SYSCTRL) authority
  • System Maintenance (SYSMAINT) authority
  • Database Administrator (DBADM) authority
  • Load (LOAD) authority

Privileges are used to convey the rights to perform certain actions on specific database resources to both individual users and groups. With DB2, two distinct types of privileges exist: database privileges and object privileges.

Database privileges apply to a database as a whole, and for most users, they act as identification that gets verified at the second security checkpoint that must be cleared before access to data is provided. Unlike database privileges, which apply to a database as a whole, object privileges only apply to specific objects within a database. These objects include schemas, table spaces, tables, indexes, views, packages, routines, sequences, servers, and nicknames.

IDS, like DB2, contains predefined authorities. These authorities are given at the database level. If a user is granted one of these authorities, the user gets a set of special privileges. The authorities are listed below.

  • Connect
  • Resource
  • DBA

The DBA privilege grants all resource privileges and all other privileges needed to maintain the database system. This is more or less like a DBADM authority in DB2 . This is the most privileged level of database access in IDS.

Some of the DB2 and IDS privileges are listed below for easy understanding. For an exhaustive list, refer to the DB2 Information Center.

Table 5. Privileges
DB2IDSRemarks
CONNECTConnect privilegeAllows a user access to the database. A user must have at least a connect privilege to have any access to the database.
CREATETAB
CREATE_EXTERNAL_ROUTINE
CREATEIN
Resource privilege Grants all connect privileges, and it allows users the ability to create new tables, indexes, and procedures.
CREATETAB
LBAC
Table level and column level privilegesAccess to specific tables and columns within tables can be controlled by the database administrator. The creator of the table, or the user with resource or DBA authority, can create tables. Different table level privileges like select, insert, delete, update, index, alter, references, and all can be granted by the database administrator.

Apart from these, IDS allows customers to create database-specific user groups called roles. After a role has been created, users are assigned to the role and further permissions are granted to the role. In IDS, roles are defined at the database level.


Locking mechanisms

To improve concurrency, DB2 and IDS use a combination of locks and isolation levels.

A lock is a mechanism that is used to associate a data resource with a single transaction, with the purpose of controlling how other transactions interact with that resource while it is associated with the owning transaction. The transaction that a locked resource is associated with is said to hold or own the lock. The DB2 database manager and IDS use locks to prohibit transactions from accessing uncommitted data written by other transactions (unless the uncommitted read isolation level is used), and to prohibit the updating of rows by other transactions when the owning transaction is using a restrictive isolation level. Once a lock is acquired, it is held until the owning transaction is terminated. At that point, the lock is released and the data resource is made available to other transactions.

In DB2, locks can be placed on database objects like table spaces, tables, and rows. IDS allows application developers to place locks on different objects, like databases, tables, pages or rows, and indexes.

Lock types

Several different types of locks are available, some of them are listed below. To learn more about locks, refer to the information centers for DB2 and Informix in the Resources section.

  • Intent None (IN)
  • Exclusive (X)
  • Update (U)
  • Super Exclusive (Z)

Lock attributes

All locks have the following basic attributes:

  • Object: Identifies the data resource that is being locked. The DB2 database manager acquires locks on data resources, such as table spaces, tables, and rows, whenever they are needed.
  • Size: Specifies the physical size of the portion of the data resource that is being locked. A lock does not always have to control an entire data resource. For example, rather than giving an application exclusive control over an entire table, the DB2 database manager can give an application exclusive control over a specific row in a table.
  • Duration: Specifies the length of time for which a lock is held. A transaction's isolation level usually controls the duration of a lock.
  • Mode: Specifies the type of access allowed for the lock owner as well as the type of access permitted for concurrent users of the locked data resource. This attribute is commonly referred to as the lock state.

Lock escalation

All locks require space for storage. Because the space available is not infinite, the DB2 database manager must limit the amount of space that can be used for locks. This is done through the maxlocks database configuration parameter. In IDS, you can control this by setting the LOCKS configuration parameter. In order to prevent a specific database agent from exceeding the lock space limitations established, a process known as lock escalation is performed automatically whenever too many locks (of any type) have been acquired. IDS also provides an option to increase the number of locks acquired dynamically.


Tools and utilities

The tools that are included with DB2 and IDS provide a whole array of time-saving, error-reducing graphical interfaces. There are a number of tools that help DBAs and application programmers in their respective roles. Only a few tools have been highlighted: common data movement utilities and data maintenance utilities.

DB2 Control Center

The Control Center is used for administering DB2 servers. It provides you with a whole picture of your instances and databases, and allows you to perform most database operations in DB2. As you can see in Figure 15, the left panel (object pane) shows you the tree structure in your local and remote systems, and the right panel (contents pane) provides more detail about the specific item selected.

Figure 15. DB2 Control Center
DB2 Control Center

Informix Server Administrator (ISA)

ISA is a Web-based cross-platform database server administration tool, used to monitor multiple Informix servers. ISA can be used to check the Informix instance configuration, display the storage information of an Informix instance, shows the information of VPs, can be used to add and create dbspaces, and can be used to monitor the performance of an Informix server.

Figure 16. Informix Server Administrator (ISA)
Informix Server Administrator (ISA)

More tools

Additional tools are briefly described below:

  • Configuration Assistant: A DB2 tool, used for setting up client/server communications and maintaining registry variables, though it can do more.
  • Configuration Advisor: Tuning a database to get optimal performance can be an overwhelming task. DB2 configuration parameters play an important role in performance, as they affect the operating characteristics of a database or database manager. The DB2 Configuration Advisor wizard gives database administrators a good starting point with initial configuration parameter settings upon which they could make improvements if they want. To see more article on this topic, refer to the Resources section.
  • Developer Workbench: DB2 9 introduces a new no-charge application development tool, based on the Eclipse framework called the DB2 Developer Workbench (DWB). The DWB is a one-stop center for creating, editing, debugging, deploying, and testing DB2 stored procedures and user-defined functions. You can also use the DWB to develop SQLJ applications, and create, edit, and run SQL statements and XML queries.
  • Onmonitor: The Onmonitor utility in IDS, can be used to create an Informix instance, to modify or view the configuration of a Informix instance, create dbspaces, add chunks to a dbspace, and check the database information.
  • Onperf: A graphical monitoring tool for IDS. The Onperf utility can be used to perform routine system monitoring and performance monitoring.

Data movement utilities

Data movement utilities are used to move data from one database to another or one environment to another, like from test to production. The data can be unloaded or exported from one database and then can be imported or loaded into another. DB2 has EXPORT, IMPORT, and LOAD utilities for this purpose. In IDS, these are called dbexport, dbimport, and dbload utilities respectively.

Other than the above mentioned utilities, DB2 provides the db2move utility to move the entire data from one database to another, and db2look to generate the DDLs and statistics. These DDLs can then be used to create the database objects in another database to replicate the database structure. Other than the above mentioned utilities, IDS provides a High Performance Loader (HPL) utility to unload and load data from ASCII files.

Data maintenance utilities

The way in which data is physically distributed across table space containers can have a significant impact on how applications that access the data perform. DB2 and IDS uses the statistics information in the catalog table to derive the best access plan.

To update statistics on all the tables or a group of tables in DB2, you can use the REORGCHK command with the UPDATE STATISTICS option. In IDS, use the UPDATE STATISTICS command to update the statistics.

db2pd and onstat

DB2 provides a utility called db2pd for collecting for DB2 instances and databases. db2pd provides more than 20 options to display information about database transactions, table spaces, table statistics, dynamic SQL, database configurations, and many other database details. A single db2pd command can retrieve multiple areas of information and can route the output to files. The utility can also be invoked a specified number of times within a specified period of time, to help you understand changes over time. Use this tool for troubleshooting, problem determination, database monitoring, performance tuning, and to aid in application development design. For more information, refer to the Resources section.

Onstat is an IDS utility that reads shared memory structures or segments and prints statistics and diagnostic information related to IDS at the time the command executes. The onstat utility can be used to monitor the performance of IDS, and has options for viewing disk reads and disk writes, buffer usage information, user level monitoring, CPU statistics, information related to LRU queues, network level statistics, and for analyzing locks.

Conclusion

This article briefly discussed various aspects of DB2 and Informix, such as editions, architecture, process and memory model, databases and storage models. You also learned how backup and restore is carried out in DB2 and Informix. Now you should be able to start leveraging your RDBM skills to explore and experiment with DB2 and Informix. You are encouraged to go through various articles and use both of the products to gain in-depth knowledge.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=192206
ArticleTitle=How to go hand-in-hand with DB2 and Informix
publish-date=01252007