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. This article shows you how to avoid out-of-space situations by using existing features of Informix® Dynamic Server (IDS) to implement space management functionality. IDS 11.5 delivers a system database called SYSADMIN that includes administrative stored procedures and an integrated DB scheduler. Together, the stored procedures and scheduler enable the database server to execute database functions and procedures at predefined times. You can use the scheduler to continuously monitor all Informix dbspaces, and to extend space when necessary. The article features a sample program that demonstrates how you can implement this function.

Share:

Krzysztof Dol (krzysztof.dol@de.ibm.com), Informix IT Specialist, IBM

Krzysztof Dol photoKrzysztof 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.



25 February 2010

Also available in Russian

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.


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.

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.

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
/dev/raw1/pathname
2GBsize
1GBoffset

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

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
${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.

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");

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]  
          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

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.


Download

DescriptionNameSize
Source code of RAWDEVSIZE.crawdevsize.zip8KB

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

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

 


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

All information submitted is secure.

Choose your display name



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

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

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

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

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=469512
ArticleTitle=Automatically extend dbspaces in Informix Dynamic Server 11.5
publish-date=02252010