How to go hand-in-hand with DB2 and Informix
How to go hand-in-hand with DB2 and Informix Skill Level: Intermediate Suma C Shastry (suma.chakrabarti@in.ibm.com) Staff Software Engineer IBM Mohan Kumar (mohankumarsp@in.ibm.com) System Software Engineer IBM Prasad Srinivasachar (srprasad@in.ibm.com) Advisory Software Engineer IBM 25 Jan 2007 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. 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 9 IDS 10 pureXML and hybrid data services Security enhancements How to go hand-in-hand with DB2 and Informix © Copyright IBM Corporation 1994, 2006. All rights reserved. Page 1 of 49 developerWorks® ibm.com/developerWorks 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. 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: 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. How to go hand-in-hand with DB2 and Informix Page 2 of 49 • XA transactions • Nested ANSI-compliant left-outer joins • Subqueries • Full-outer joins SQL enhancements The SQL enhancement improves database availability. Creating and dropping indexes without locking tables. © Copyright IBM Corporation 1994, 2006. All rights reserved. ibm.com/developerWorks 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. developerWorks® Enterprise replication enhancements The Enterprise replication enhancements ease administration, improve data integrity, and allow additional SQL operations. Application development Backup and restore enhancements enhancement The backup and restore enhancements Application development improve performance and debugging. 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. 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 How to go hand-in-hand with DB2 and Informix © Copyright IBM Corporation 1994, 2006. All rights reserved. Page 3 of 49 developerWorks® ibm.com/developerWorks 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 9 IDS 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: 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: • Large data warehouses of multiple terabyte size • High-performing, 24x7 available, high-volume transaction processing business solutions • • Enterprise data replication • High availability data replication Web-based solutions Additionally, DB2 ESE 9 offers connectivity, compatibility, and integration with other Enterprise DB2 and IDS data How to go hand-in-hand with DB2 and Informix Page 4 of 49 © Copyright IBM Corporation 1994, 2006. All rights reserved. ibm.com/developerWorks developerWorks® sources. 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 desktopor 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 How to go hand-in-hand with DB2 and Informix © Copyright IBM Corporation 1994, 2006. All rights reserved. Page 5 of 49 developerWorks® ibm.com/developerWorks the instance DB2, and another database MYDB2 is associated with a different instance MYINST. Instances allow users to have separate, independent environments for production, test, and development purposes. 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 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 How to go hand-in-hand with DB2 and Informix Page 6 of 49 © Copyright IBM Corporation 1994, 2006. All rights reserved. ibm.com/developerWorks developerWorks® Environment Variables INFORMIXDIR Installation Location ONCONFIG Instance Configuration File INFORMIXSERVER Instance Name INFORMIXSQLHOSTS Name of a file containing Instance host&port info How to go hand-in-hand with DB2 and Informix © Copyright IBM Corporation 1994, 2006. All rights reserved. Page 7 of 49 developerWorks® ibm.com/developerWorks 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 How to go hand-in-hand with DB2 and Informix Page 8 of 49 © Copyright IBM Corporation 1994, 2006. All rights reserved. ibm.com/developerWorks developerWorks® 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 How to go hand-in-hand with DB2 and Informix © Copyright IBM Corporation 1994, 2006. All rights reserved. Page 9 of 49 developerWorks® ibm.com/developerWorks 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: How to go hand-in-hand with DB2 and Informix Page 10 of 49 © Copyright IBM Corporation 1994, 2006. All rights reserved. ibm.com/developerWorks developerWorks® • 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) How to go hand-in-hand with DB2 and Informix © Copyright IBM Corporation 1994, 2006. All rights reserved. Page 11 of 49 developerWorks® ibm.com/developerWorks 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 How to go hand-in-hand with DB2 and Informix Page 12 of 49 © Copyright IBM Corporation 1994, 2006. All rights reserved. ibm.com/developerWorks developerWorks® 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. How to go hand-in-hand with DB2 and Informix © Copyright IBM Corporation 1994, 2006. All rights reserved. Page 13 of 49 developerWorks® ibm.com/developerWorks 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 How to go hand-in-hand with DB2 and Informix Page 14 of 49 © Copyright IBM Corporation 1994, 2006. All rights reserved. ibm.com/developerWorks developerWorks® 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 How to go hand-in-hand with DB2 and Informix © Copyright IBM Corporation 1994, 2006. All rights reserved. Page 15 of 49 developerWorks® ibm.com/developerWorks 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 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. How to go hand-in-hand with DB2 and Informix Page 16 of 49 © Copyright IBM Corporation 1994, 2006. All rights reserved. ibm.com/developerWorks developerWorks® • 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 How to go hand-in-hand with DB2 and Informix © Copyright IBM Corporation 1994, 2006. All rights reserved. Page 17 of 49 developerWorks® ibm.com/developerWorks 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 How to go hand-in-hand with DB2 and Informix Page 18 of 49 © Copyright IBM Corporation 1994, 2006. All rights reserved. ibm.com/developerWorks developerWorks® 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). How to go hand-in-hand with DB2 and Informix © Copyright IBM Corporation 1994, 2006. All rights reserved. Page 19 of 49 developerWorks® ibm.com/developerWorks 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 How to go hand-in-hand with DB2 and Informix Page 20 of 49 © Copyright IBM Corporation 1994, 2006. All rights reserved. ibm.com/developerWorks developerWorks® 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 How to go hand-in-hand with DB2 and Informix © Copyright IBM Corporation 1994, 2006. All rights reserved. Page 21 of 49 developerWorks® ibm.com/developerWorks 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') How to go hand-in-hand with DB2 and Informix Page 22 of 49 © Copyright IBM Corporation 1994, 2006. All rights reserved. ibm.com/developerWorks developerWorks® 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 How to go hand-in-hand with DB2 and Informix © Copyright IBM Corporation 1994, 2006. All rights reserved. Page 23 of 49 developerWorks® ibm.com/developerWorks 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, How to go hand-in-hand with DB2 and Informix Page 24 of 49 © Copyright IBM Corporation 1994, 2006. All rights reserved. ibm.com/developerWorks developerWorks® 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 How to go hand-in-hand with DB2 and Informix © Copyright IBM Corporation 1994, 2006. All rights reserved. Page 25 of 49 developerWorks® ibm.com/developerWorks 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 How to go hand-in-hand with DB2 and Informix Page 26 of 49 © Copyright IBM Corporation 1994, 2006. All rights reserved. ibm.com/developerWorks developerWorks® Organization. The ontape utility backs up the dbspaces sequentially, where as onbar can backup dbspaces in parallel. Figure 10. 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. How to go hand-in-hand with DB2 and Informix © Copyright IBM Corporation 1994, 2006. All rights reserved. Page 27 of 49 developerWorks® ibm.com/developerWorks 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: How to go hand-in-hand with DB2 and Informix Page 28 of 49 © Copyright IBM Corporation 1994, 2006. All rights reserved. ibm.com/developerWorks developerWorks® 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 How to go hand-in-hand with DB2 and Informix © Copyright IBM Corporation 1994, 2006. All rights reserved. Page 29 of 49 developerWorks® ibm.com/developerWorks • 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 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 How to go hand-in-hand with DB2 and Informix Page 30 of 49 © Copyright IBM Corporation 1994, 2006. All rights reserved. ibm.com/developerWorks developerWorks® • 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 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 How to go hand-in-hand with DB2 and Informix © Copyright IBM Corporation 1994, 2006. All rights reserved. Page 31 of 49 developerWorks® ibm.com/developerWorks 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 How to go hand-in-hand with DB2 and Informix Page 32 of 49 © Copyright IBM Corporation 1994, 2006. All rights reserved. ibm.com/developerWorks developerWorks® 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. How to go hand-in-hand with DB2 and Informix © Copyright IBM Corporation 1994, 2006. All rights reserved. Page 33 of 49 developerWorks® ibm.com/developerWorks In DB2 rollforward rollforward rollforward In IDS onbar onbar -r -r database sample to end of logs and complete database sample to timestamp and complete database sample to timestamp using local time and complete -t -l time logid Security features Both the DB2 and IDS security model consist of two main components: authentication and authorization. Figure 13. 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 How to go hand-in-hand with DB2 and Informix Page 34 of 49 © Copyright IBM Corporation 1994, 2006. All rights reserved. ibm.com/developerWorks developerWorks® 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 Type Description SERVER Authentication takes place on the server. SERVER_ENCRYPT Authentication takes place on the server. Passwords are encrypted at the client machine before being sent to the server. How to go hand-in-hand with DB2 and Informix © Copyright IBM Corporation 1994, 2006. All rights reserved. Page 35 of 49 developerWorks® ibm.com/developerWorks CLIENT Authentication takes place on the client machine. *KERBEROS Authentication 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_ENCRYPT Authentication 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_CMP Authentication 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. GSSPLUGIN Authentication 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 How to go hand-in-hand with DB2 and Informix Page 36 of 49 © Copyright IBM Corporation 1994, 2006. All rights reserved. ibm.com/developerWorks developerWorks® 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 Method Attributes Description OS user ID No Encryption, IDS has always used this basic uses OS password authentication. This technique uses an lookup 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 encryption OS 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 How to go hand-in-hand with DB2 and Informix © Copyright IBM Corporation 1994, 2006. All rights reserved. Page 37 of 49 developerWorks® ibm.com/developerWorks registries and the conscm.cfg file. Pluggable Authentication Model (PAM) User-provided authentication methods Authentication 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 directory LDAP 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 How to go hand-in-hand with DB2 and Informix Page 38 of 49 © Copyright IBM Corporation 1994, 2006. All rights reserved. ibm.com/developerWorks developerWorks® 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 DB2 IDS Remarks CONNECT Connect privilege Allows a user access to the database. A user must have at least a connect privilege to have any access to the database. CREATETAB Resource privilege CREATE_EXTERNAL_ROUTINE CREATEIN CREATETAB LBAC Grants all connect privileges, and it allows users the ability to create new tables, indexes, and procedures. Table level and Access to specific tables and column level privileges 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. How to go hand-in-hand with DB2 and Informix © Copyright IBM Corporation 1994, 2006. All rights reserved. Page 39 of 49 developerWorks® ibm.com/developerWorks 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 How to go hand-in-hand with DB2 and Informix Page 40 of 49 © Copyright IBM Corporation 1994, 2006. All rights reserved. ibm.com/developerWorks developerWorks® 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 How to go hand-in-hand with DB2 and Informix © Copyright IBM Corporation 1994, 2006. All rights reserved. Page 41 of 49 developerWorks® ibm.com/developerWorks 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) How to go hand-in-hand with DB2 and Informix Page 42 of 49 © Copyright IBM Corporation 1994, 2006. All rights reserved. ibm.com/developerWorks developerWorks® 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 How to go hand-in-hand with DB2 and Informix © Copyright IBM Corporation 1994, 2006. All rights reserved. Page 43 of 49 developerWorks® ibm.com/developerWorks 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. How to go hand-in-hand with DB2 and Informix Page 44 of 49 © Copyright IBM Corporation 1994, 2006. All rights reserved. ibm.com/developerWorks developerWorks® 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 Share this... Digg this story Post to del.icio.us Slashdot it! 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. How to go hand-in-hand with DB2 and Informix © Copyright IBM Corporation 1994, 2006. All rights reserved. Page 45 of 49 developerWorks® How to go hand-in-hand with DB2 and Informix Page 46 of 49 ibm.com/developerWorks © Copyright IBM Corporation 1994, 2006. All rights reserved. ibm.com/developerWorks developerWorks® Resources Learn • IBM DB2 Database for Linux, UNIX, and Windows Information Center: Find information that you need to use the DB2 family of products and features. • "What's new in DB2 9" (developerWorks, February 2006): Explore Db2 9's new XML technology and learn why IBM now considers DB2 a "hybrid" or multi-structured DBMS. • DB2 architecture and process overview: Get general information about DB2 architecture and processes can help you understand detailed information provided for specific topics. • Explore more about DB2 9 features: • "pureXML in DB2 9: Which way to query your XML data?" (developerWorks, June 2006) • "DB2 Label-Based Access Control, a practical guide, Part 1: Understand the basics of LBAC in DB2" (developerWorks, May 2006) • "Introducing DB2 9, Part 2: Table partitioning in DB2 9" (developerWorks, May 2006) • "Row compression in DB2 9" (developerWorks, October 2006) • "DB2 9 self-tuning memory management" (developerWorks, November 2006): Walk through a series of exercises to understand and learn how to administer this new feature. • "DB2 Developer Workbench, Part 1: Developer Workbench concepts and basic tasks" (developerWorks, August 2006) • "DB2 performance tuning using the DB2 Configuration Advisor" (developerWorks, May 2004): Familiarize yourself with various database configuration parameters and the use of the Configuration Advisor wizard in performance tuning. • "The db2pd tool" (developerWorks, April 2005): Learn how to use this tool to keep track of transactions, tablespaces, table statistics, dynamic SQL, and all your configuration settings. • DB2 Express-C, the no-charge version of DB2 Express Edition for the community. • IBM Informix Dynamic Server v10.0 Information Center Find more information on IDS v10.0. How to go hand-in-hand with DB2 and Informix © Copyright IBM Corporation 1994, 2006. All rights reserved. Page 47 of 49 developerWorks® ibm.com/developerWorks • Informix library: Find hard copy versions of many Informix manuals. • "New features in IBM Informix Dynamic Server, Version 10.0" (developerWorks, March 2005): Learn some of the key features of IBM IDS, v10.0. • "Optimize your BAR Performance using parallel backups with Informix Dynamic Server" (developerWorks, November 2006): Read about ON-Bar, the parallel backup and restore sequence, and find a discussion of configuration parameters and dbspace sizing to achieve your performance objectives. • "Compare Informix Dynamic editions" (developerWorks, April 2006): Understand the basic licensing rules, functions, and feature differences between the members of the IDS server family. • Visit developerWorks Informix Zone to read articles and tutorials • developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more. • Stay current with developerWorks technical events and webcasts. Get products and technologies • Download a free trial version of DB2 Enterprise Server Edition. • Download a free trial version of Informix Dynamic Server.. • Build your next development project with IBM trial software, available for download directly from developerWorks. Discuss • Participate in the discussion forum for this content. • Participate in developerWorks blogs and get involved in the developerWorks community. About the authors Suma C Shastry Suma 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. How to go hand-in-hand with DB2 and Informix Page 48 of 49 © Copyright IBM Corporation 1994, 2006. All rights reserved. ibm.com/developerWorks developerWorks® Mohan Kumar Mohan 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 Prasad 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. How to go hand-in-hand with DB2 and Informix © Copyright IBM Corporation 1994, 2006. All rights reserved. Page 49 of 49
© Copyright 2024