Automatically extend dbspaces in Informix Dynamic Server 11.5

A mechanism for managing disk space to avoid an out-of-space condition


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.

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 Related topics section contains links you can use to obtain detailed information about the ph_task table and ph_threahold table.

Figure 1. Continuous monitoring of dbspaces
Every 30 seconds a task checks if       free space for a dbspace is below the threshold. If so, the space is extended.
Every 30 seconds a task checks if free space for a dbspace is below the threshold. If so, the space is extended.

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.

Table 1. Parameters for adding a chunk
Parameter ValueMeaning
dbs1adbspace name

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:

  • name as LVARCHAR (1024)
  • offset as INT8
  • freesize as INT8

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).

Figure 2. Analysis of raw devices
Free and used chunks for raw devices RD1, RD2,       and RD3 in the /chunks directory
Free and used chunks for raw devices RD1, RD2, and RD3 in the /chunks directory

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:

Table 2. Resulting table free_chunks
TableData 1Data 2
/chunks/RD10 GB100 MB
/chunks/RD10,4 GB700 MB
/chunks/RD11,6 GB400 MB
/chunks/RD20 GB700 MB
/chunks/RD21,4 GB600 MB
/chunks/RD30 GB300 MB
/chunks/RD31,2 GB800 MB

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.

Listing 1. RAWDEVSIZE program raw device links
          |--- 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.

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",

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)
    '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
    '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';
                     TRUNC (SUM (ch.nfree * ch.pagesize) / 1024, 0)
        INTO dbspace_name, dbspace_free
        FROM sysmaster:sysdbspaces db, sysmaster:syschunks ch
        WHERE db.dbsnum = ch.dbsnum
        AND NOT IN ('logdbs', 'physdbs', 'rootdbs', 'psapsystem')
        AND 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_object_type, alert_object_name,
      	 alert_message, alert_action)
      	VALUES (task_id, task_seq, 'ERROR', 'red',
          'DBSPACE', dbspace_name,
          'Dbspace ['||TRIM (dbspace_name) ||' - '||dbspace_free::INTEGER ||' KB free]  
          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/';
          END FOREACH
        END IF
  5. Create a wrapper for the RAWDEVSIZE program.

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

    Listing 6. Create a wrapper
    export SAPDBA_RAWDEV;
    export DIR;
    cd ${DIR};
    ${DIR}/rawdevsize.exe > ${DIR}/rawdevsize.txt


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.

Downloadable resources

Related topics


Sign in or register to add and subscribe to comments.

Zone=Information Management
ArticleTitle=Automatically extend dbspaces in Informix Dynamic Server 11.5