Informix11.70 System Management: space management (I)

In this tutorial, you will learn how to configure and manage buckets on the IBMInformix database, the utilities for creating these buckets, and how to use fragments and various features to optimize the storage in the database. This tutorial helps you prepare for part 1 of the Informixv11.70 system management certification test 919.

In this tutorial, you will learn how to configure and manage buckets on the IBM Informix database and create utilities for these buckets, and how to use fragments and various features to optimize the storage in the database. This tutorial helps you prepare for part 1 of the Informix v11.70 system management certification test 919.

Are you considering Obtaining System Management Certification for Informix 11.70 (exam 919 )? If yes, you have come to the right place. This certification preparation series covers all topics you need to understand before taking the test. Even if you do not have the idea of getting certification, this series is also a good place for you to learn about the new features in Informix 11.70.

About this tutorial

In this tutorial, you will learn how to use the IBM Informix tool to create dbspace, sbspace, and temporal dbspace. You will learn how to create and manage table fragments and use storage optimization features, such as compression. The materials provided here mainly cover the Goals in section 2nd of the exam (called space management.

Target

After completing this tutorial, you should be able:

• Create dbspace and sbspace
• Add Block areas to dbspace or sbspace
• Rename dbspace
• Mirror A dbspace or sbspace in the Block Area
• Use table fragments
• Add or delete fragments to or from the shard table by attaching or detaching
• Use Data Compression to optimize Storage

Prerequisites

To understand the content described in this tutorial, you must be familiar with the following:

• IBM Informix Environment (configuration files and parameters, installation and management)
• Commands (onstat, onmode, oncheck, and dbschema)
• IBM Informix concepts and terms (dbspace, block area, physical log, logical log, checkpoint, etc)

System Requirements

You can complete this tutorial without having to copy IBM Informix. However, if the free trial version of Informix Innovator-C Edition is used during the course of this tutorial, you will learn more from this tutorial.

Configure and manage buckets

A bucket is a physical region where IBM Informix stores data. There are some different types of storage space available for IBM Informix, including dbspace, blob space, smart blob space and external space. You can create up to 32,766 buckets on the database server.

The following utilities help you manage buckets:

• Onspaces Utility
• SQL management API commands
• OpenAdmin Tool (OAT)

Understanding dbspace

Dbspace is a logical unit, which can contain 1 to 32,766 blocks. Store databases, tables, logical log files, and physical logs in dbspace.

Use the default page size dbspace

To use onspace to create a standard dbspace, you must Log On As informix or root. On Windows, users in the Informix-Admin group can create a dbspace. Make sure that the database server is in online, management, or sleep mode.

Use the onspaces-c-d option to create a dbspace or temporary dbspace, as shown in Listing 1.

List 1. Create a dbspace

onspaces -c -d < dbspace_name> -p < pathname ordrive> -o < offset> -s < size> -m--< pathname> < offset> 

The maximum size of a dbspace is equal to the maximum number of blocks multiplied by the maximum size of a block area. (The maximum number of blocks is 32,766 per instance. The maximum size of a block area is equal to 2147483647 pages multiplied by the page size .)

Listing 2 shows how to create a 10 MB image dbspace dbspce1, which has a 5000 KB offset in both the primary and mirror blocks; use the default page size; use the original disk space on UNIX.

List 2. Create a sample image dbspace

onspaces -c -d dbspce1 -p /dev/raw_dev1 -o 5000 -s 10240 -m/dev/raw_dev2 5000 

Listing 3 shows how to create a 5 MB dbspace dbspc3 on Windows with an offset of 200 KB from the original disk space (drive e.

Listing 3. Example dbspace Creation

onspaces -c -d dbspc3 \\.\e: -o 200 -s 5120

Dbspace with non-default page size

If you want to make the key length longer than the key length that can be used for the default page size, you can specify a page size for the standard or temporary dbspace. The Root dbspace has the default page size. If you want to specify the page size, the size must be an integer multiple of the default page size and cannot exceed 16 KB.

For systems with ample storage space, the performance advantages of larger page sizes include:

• Reduces the index depth of B-type trees, even for small index keys.
• Shorten the checkpoint time

You can also perform the following operations to gain additional performance advantages:

• Groups long rows that span multiple default page sizes on the same page.
• Define a different page size for the temporary table so that the temporary table has an independent buffer pool.

You can use the BUFFERPOOL configuration parameters to create a buffer pool corresponding to the page size of dbspace. The table can be placed in one dbspace, and the index of the table can be placed in another dbspace. The page size of these partitions can be different. You can perform the following steps to specify the page size of dbspace.

1. If this mode is not enabled, use the onmode-BC command to enable the bulk mode. By default, when IBM Informix is initialized for the first time or restarted, IBM Informix first enables the large block mode. For information about the onmode utility, see the IBM Informix administrator reference guide.
2. Create a buffer pool corresponding to the page size of dbspace. You can use the onparams utility or BUFFERPOOL to configure parameters. This operation should be performed before dbspace is created. If the size of the dbspace page you created does not have a buffer pool, IBM Informix automatically creates a buffer pool using the default parameters defined in the onconfig configuration file. There cannot be multiple buffer pools with the same page size.
3. Define the page size when creating a dbspace. You can use the onspaces utility or ON-Monitor. For example, if you create a dbspace with 6 KB page size, you must create a 6 KB buffer pool. If the page size is not specified for the new buffer pool, IBM Informix uses the default page size of the Operating System (4 KB for Windows and 2 KB for most UNIX platforms) as the default page size of the buffer pool.