Introduction

As productive databases containing important information systems rapidly grow larger and larger, automated space management becomes more and more important. Although Informix Dynamic Server (IDS) does not currently offer an integrated space management feature, it does already include tools and capabilities that you can use to avoid outages caused by running out of disk space.

Here is an overview of the key tasks you can perform to get this type of functionality:

1. Schedule a regular task to monitor the available free space in all dbspaces.
2. Implement the space check function as an SQL stored procedure.
3. If the free size of a dbspace is lower than a specified threshold, automatically extend it by calling the admin() function with the ADD CHUNK administrative command.

   You use the sample RAWDEVSIZE program contained in the Download section to determine the size and offset parameters of the free parts of all existing RAW devices and store that information in a database table. The SQL stored procedure that executes the ADD CHUNK command reads these results.

Back to top

Continuous monitoring of dbspaces

To implement continuous monitoring of dbspaces, schedule a regular task by adding a row to your ph_task table.

Figure 1 depicts an example where a task to execute an SQL stored procedure is scheduled to run every 30 seconds. If the task discovers that the free space for a dbspace has fallen below a certain threshold, it can then extend the dbspace. In the example described in this article, the threshold is defined in the ph_threshold system table.

The Resources section contains links you can use to obtain detailed information about the ph_task table and ph_threahold table.

Back to top

Adding a chunk to a dbspace

You can use the admin() administrative function delivered with the SYSADMIN database to extend dbspaces using SQL.

Following is an example for adding a chunk to a dbspace (in this case, using a raw device ):

    
EXECUTE FUNCTION admin ("ADD CHUNK", "dbs1a", "/dev/raw1/", "2GB", "1GB");

Table 1 describes the parameters used in the above command.

Back to top

The sample RAWDEVSIZE program

In order to extend a dbspace, you have to be able to determine the path, size, and offset parameters of a free chunk on an existing raw device. In the example described in this article, this is done using an executable program named RAWDEVSIZE.

The Download section contains a link to the source code of the RAWDEVSIZE program. The program is written in the C language. After you download the sample program source code, you can compile it on any UNIX® platform.

The RAWDEVSIZE program analyzes available raw devices and stores the offset, size, and path information of free blocks into a database table named free_chunks. The SQL stored procedure searches for a matching free chunk in the free_chunks table and uses this information for the ADD CHUNK function call. Whenever a space extension takes place, it is necessary to update the information about the free chunks. Therefore, the RAWDEVSIZE program is called from within the SQL stored procedure following each dbspace extension.

The free_chunks table

The RAWDEVSIZE program calculates sizes of all available raw devices for Informix databases as follows:

• Sizes of raw devices in use, that do not contain any free space
• Sizes of raw devices in use, that still contain free parts
• Sizes of completely free raw devices

The collected data about the free chunks is stored in a database table named free_chunks that has the following format:

Figure 2 shows a sample set of three raw devices that contain both free chunks and chunks that are in use. The devices are named RD1, RD2, and RD3, and are in the /chunks directory. For each chunk, the figure indicates the offset value of the chunk (in GB). For free chunks, the figure also shows the size of the chunk (in MB).

Running the RAWDEVSIZE program against the raw devices depicted in Figure 2 would result in the following data being put into the free_chunks table:

The free_chunks table is sorted by the freesize of the chunk. The SQL Stored Procedure named CHECK_DBSPACE_RD determines which chunk listed in the free_chunks table should be used to extend a dbspace.

RAWDEVSIZE raw device links

The RAWDEVSIZE program was originally developed for SAP system environments, so the source code prototype is based on an SAP-specific convention regarding the raw device links.

${SAPDBA_RAWDEV} =
/informix/QI1/sapdata
          |
          |
          |--- physdev1
          |       |
          |       |
          |       |----- data1 -> /dev/rlvINF1
          |       |
          |       | 
          |        ----- data2 -> /dev/rlvINF2
          |
          |
          |--- physdev2
          |       |
          |       |
          |       |----- data1 -> /dev/rlvINF3
          |       |
          |       | 
          |        ----- data2 -> /dev/rlvINF4
          |
          |
           --- physdev3
                  |
                  |
                  |----- data1 -> /dev/rlvINF5
                  |
                  | 
                   ----- data2 -> /dev/rlvINF6
                   

A list of all available raw devices is determined by scanning the subdirectory structure below $SAPDBA_RAWDEV for links to raw devices. Then the Informix database is queried to determine the location of already used chunks on the raw devices. The program then calculates the location and size of the free chunks.

Note: In other environments, the described mechanism for determining raw devices may not be suitable. However, there may be other ways to determine the raw devices.

Overview of RAWDEVSIZE processing steps

Following is an overview of the processing steps that take place in the RAWDEVSIZE program:

1. Check the $SAPDBA_RAWDEV variable in the environment and search for the directories and files below it.
2. Check if the found data files are links to raw (character-special) devices.
3. Check the sizes of the raw devices and save the results into the file named raw_devs.unl.
4. Read data from the Informix SYSCHUNKS system table and include mirror chunks according to this criteria:

   SELECT... like '%sapdata/physdev%/data%'

   
   
5. Save the results from the previous step into the file named syschk.unl.
6. Calculate the sizes of the raw devices that are free (available) and save the results into the file named free_chunks.unl.
7. Create the contents of the free_chunks result table by loading the free_chunks.unl file.

Back to top

Using cooked files on Linux and Windows

In cases where you are using cooked files in a Linux® or Windows® environment, offset information is not relevant. Therefore, each chunk that you add has to have a unique pathname that is created by the SQL stored procedure. For example, you could use the dbspace name concatenated with a unique number as shown below:

EXECUTE FUNCTION admin ("ADD CHUNK", "mydbspace", "c:/Informix/chunks/mydbspace.001", "2GB");
|-------10--------20--------30--------40--------50--------60--------70--------80--------9|
|-------- XML error:  The previous line is longer than the max of 90 characters ---------|

Back to top

Setting up the space management example

Follow the steps is this section to set up the space management example.

1. Set up the result tables.

   As user Informix, create the result tables for RAWDEVSIZE in the SYSADMIN database, as shown in Listing 2:

   
   
   Listing 2. Setting up the result tables
   

   CREATE TABLE raw_devs ( name LVARCHAR (1024), size INT8 ); CREATE TABLE free_chunks ( name LVARCHAR (1024), offset INT8, freesize INT8 );

   
   
2. Fill the scheduler's system tables.

   Insert a row into the ph_threshold system table in the SYSADMIN database to define sample thresholds for dbspace free space, as shown in the example in Listing 3:

   
   
   Listing 3. Inserting values into the ph_threshold system table
   

   INSERT INTO ph_threshold (name, task_name, value, value_type, description) VALUES ( 'DBSPACE ALERT', 'check_dbspace', '10000', 'NUMERIC', 'A dbspace should be extended if free space lower than 10 MB.' );

   
   
3. Schedule a regular task by inserting a row into the ph_task system table in the SYSADMIN database, as shown in the example in Listing 4:
   
   Listing 4. Inserting a row into the ph_task system table
   

   INSERT INTO ph_task ( tk_name, tk_type, tk_group, tk_description, tk_execute, tk_frequency, tk_delete ) VALUES ( 'check_dbspace', 'TASK', 'SERVER', 'Checking of dbspaces, which should be min. 10 MB free.', 'check_dbspace_rd', <-- Name of the SQL stored procedure that should be called '0 0:00:30', <-- Calling interval - every 30 sec. '7 0:00:00' );

   
   
4. Create an SQL stored procedure.

   Create a stored procedure named check_dbspace_rd in the SYSADMIN database, as shown in Listing 5:

   
   
   Listing 5. Creating the check_dbspace_rd SQL stored procedure
   

   CREATE PROCEDURE check_dbspace_rd (task_id INTEGER, task_seq INTEGER) DEFINE dbs_min_size INTEGER; DEFINE dbspace_name VARCHAR(128); DEFINE dbspace_free INT8; DEFINE i INTEGER; DEFINE path LVARCHAR(1024); DEFINE offs VARCHAR(64); SELECT value::INTEGER INTO dbs_min_size FROM ph_threshold WHERE name = 'DBSPACE ALERT'; FOREACH SELECT db.name, TRUNC (SUM (ch.nfree * ch.pagesize) / 1024, 0) free_size_KB INTO dbspace_name, dbspace_free FROM sysmaster:sysdbspaces db, sysmaster:syschunks ch WHERE db.dbsnum = ch.dbsnum AND db.name NOT IN ('logdbs', 'physdbs', 'rootdbs', 'psapsystem') AND db.name NOT LIKE 'tmpdbs%' GROUP BY 1 IF dbspace_free < dbs_min_size THEN INSERT INTO ph_alert (alert_task_id, alert_task_seq, alert_type, alert_color, alert_state, alert_object_type, alert_object_name, alert_message, alert_action) VALUES (task_id, task_seq, 'ERROR', 'red', 'ADDRESSED', 'DBSPACE', dbspace_name, 'Dbspace ['||TRIM (dbspace_name) ||' - '||dbspace_free::INTEGER ||' KB free] |--10--------20--------30--------40--------50--------60--------70--------80--------9| |-------- XML error: The previous line is longer than the max of 90 characters ---------| should be extended!', NULL ); FOREACH SELECT FIRST 1 name, offset INTO path, offs FROM free_chunks ORDER BY freesize LET offs = offs ||' B'; EXECUTE FUNCTION admin ('ADD CHUNK', dbspace_name, path, '200 MB', offs) INTO i; SYSTEM '/informix/QI1/etc/sysadmin/rawdevsize/rawdevsize.sh'; END FOREACH END IF END FOREACH END PROCEDURE;

   
   
5. Create a wrapper for the RAWDEVSIZE program.

   For example, create a script named rawdevsize.sh as a wrapper that calls the RAWDEVSIZE program from within the SQL stored procedure, as shown in Listing 6.

   
   
   Listing 6. Create a wrapper
   

   #!/bin/sh SAPDBA_RAWDEV=/informix/QI1/sapdata; export SAPDBA_RAWDEV; DIR=/informix/QI1/etc/sysadmin/rawdevsize; export DIR; cd ${DIR}; ${DIR}/rawdevsize.exe > ${DIR}/rawdevsize.txt

   
   

Back to top

Summary

This article describes a procedure you can use to provide automated extensions of Informix dbspaces with IDS 11.5. The procedure takes advantage of the sysadmin database within IDS 11.5, monitors the freespace, and automatically adds chunks to the dbspaces that are running full. By implementing this mechanism, you can ensure that you do not encounter "dbspace full" problems. DB2 provides several mechanisms to automatically add space to a tablespace when required, but at this point, IDS does not have any similar integrated feature. The procedure described in this article helps you address this pain point.

Back to top

Download

Information about download methods

Resources

Learn

• Learn more about ph_task in the Informix Dynamic Server Information Center.
  
  
• Learn more about ph_threshold in the Informix Dynamic Server Information Center.
  
  
• Use the Informix area on developerWorks, to get the resources you need to advance your skills in on Informix Dynamic Server.
  
  

Get products and technologies

• Download a trial version of Informix Dynamic Server to test the concepts in this article.
  
  
• Download IBM product evaluation versions or explore the online trials in the IBM SOA Sandbox and get your hands on application development tools and middleware products from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Check out developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

Krzysztof Dol has worked since January 2001 on the SAP / Informix Integration Team located in Walldorf, Germany, at the SAP headquarters. He is responsible for a number of tools used for the SAP Upgrade process and for the certification of new IDS releases and Fixpaks with SAP.

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Continuous monitoring of dbspaces
• Adding a chunk to a dbspace
• The sample RAWDEVSIZE program
• Using cooked files on Linux and Windows
• Setting up the space management example
• Summary
• Download
• Resources
• About the author
• Comments

Next steps from IBM

• Try: Informix Ultimate Edition
• Buy: Informix Ultimate Edition

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers