Thursday, August 5, 2010

Autoextenze: Dbspace Enhancement for Informix. No Gimmick . . . Just Science!


Recently Google alerted me about new Informix sutff on the web, this time it was a question on stackoverflow.com from an Oracle DBA who was also administering an Informix engine and wanted to know if Informix had autoextend functionality for chunks similar to what Oracle has for datafiles in a tablespace. It was late, but I was up playing some poker trying to kill time before a 2 AM maintenance so I replied.

I answered with No, there isn't anything built into the engine but you should be able to use the SQL Admin API and the Informix Scheduler to accomplish the same thing. I've never implemented anything like this and I don't know if anyone else has, but it seemed like an interesting project so I gave it a shot.

My Informix Autoextend functionality consists of:
  • A Stored Procedure that is run by the Scheduler and adds a chunk to a dbspace if needed
  • A Stored Procedure that executes the ALARMPROGRAM to notify us when a chunk is added
  • A modification to alarmprogram.sh to capture my new Event Classes for autoextend
  • A Task run by the Scheduler for checking each dbspace I want to autoextend
And of course there are some limitations:
  • My autoextend only adds cooked chunks
  • Autoextend does not work for environments participating in HDR or RSS
  • This is only something I did to see if I could do it. Use at your own risk. It is probably better to size your dbspaces appropriately, monitor usage and manually add chunks as needed
A Stored Procedure to Execute ALARMPROGRAM

When I think about something adding chunks to my engine all willy nilly I start to get a little nervous. That's my job, damn it, who do you think you are anyway, Mr. Computer? Well, if you're going to do something like that you should at least tell me.

I figured the ALARMPROGRAM and event alarms would be the best way for this autoextend process to keep me informed of what it was doing to my engine. I searched for a way to raise event alarms from SPL, but was unsuccessful. I decided to simply call the ALARMPROGRAM myself from a stored procedure to get around this. I could have implemented this logic inside the stored procedure that checks and add chunks, but I figured I might be able to reuse this functionality someday if I put it into a separate function.

I also grappled with where these functions should live. I would be creating functions for engine administration, so it doesn't seem right to create them in a database that contains user data, like my blog database. I also didn't want to create them in sysmaster or sysadmin because that feels dirty. That's IBM's playground. Maybe I'm being weird, but I created a new database called sysextend just to hold these 2 stored procedures. Hey, it's no worse than the sysuser database. Oh and I put it in the rootdbs, because when do you ever get to put something the rootdbs? Carpe Diem.

Here is the stored procedure to call the script defined by ALARMPROGRAM in the ONCONFIG.
create database sysextend in rootdbs with log;

create procedure call_alarmprogram(s_severity smallint, s_class smallint, v_msg varchar(255), v_text varchar(255), v_file varchar(255))
   define v_alarmprogram varchar(255);
   define v_arguments varchar(255);

   let v_arguments = s_severity || " " || s_class || " '" || v_msg || "' '" || v_text || "' '" || v_file || "'";

   select
      cf_effective
   into
      v_alarmprogram
   from
      sysmaster:sysconfig
   where
      cf_name = "ALARMPROGRAM";

   system v_alarmprogram || " " || v_arguments;
end procedure;
What's going on here? The stored procedure accepts as arguments the 5 things the ALARMPROGRAM expects to be passed in on the command line, Event Severity, Event Class, Event Message, Event Text and Event File.  I then build a list of these arguments to be sent on the command line to alarmprogram.sh like it expects. I then query sysconfig to get the full pathname of whatever is defined for ALARMPROGRAM in the ONCONFIG, just in case some gremlins got in there and changed it from /opt/informix/etc/alarmprogram.sh. Gremlins suck and they attack when you least expect it. Finally I use system to execute the script.

A Stored Procedure to Add a Chunk

This is where the magic happens. I created a stored procedure to check the number of free pages in a dbspace and if a new chunk is needed I use the SQL Admin API to add a chunk and send an event to alarmprogram.sh. You know what is neat? The SQL Admin API will create the chunk file if it does not already exist. No more touch/chown/chmod if you don't want to. Well, I think it is neat.
create procedure autoextend_dbspace(v_dbspacename varchar(255), b_min_kb_free bigint, b_kb_to_add bigint, v_chunk_basename varchar(255))
   define b_kb_free bigint;
   define v_chunk_pathname varchar(255);
   define v_alarm_text varchar(255);
   define i_admin_rc integer;

   select
      sum(c.nfree * 2),
      rtrim(v_chunk_basename) || "." || rtrim(to_char(current, "%Y%m%d.%H%M%S"))
   into
      b_kb_free,
      v_chunk_pathname
   from
      sysmaster:sysdbspaces d,
      sysmaster:syschunks c
   where
      d.dbsnum = c.dbsnum and
      d.name = v_dbspacename;

   if b_kb_free < b_min_kb_free then
      let v_alarm_text = "dbspace: " || rtrim(v_dbspacename) || ", chunk path: " || rtrim(v_chunk_pathname) || ", size: " || b_kb_to_add || " KB";
      execute function sysadmin:admin("add chunk", rtrim(v_dbspacename), rtrim(v_chunk_pathname), b_kb_to_add, 0) into i_admin_rc;
      if i_admin_rc >= 0 then
         execute procedure call_alarmprogram(2, 101, "chunk added to dbspace", v_alarm_text, "");
      else
         execute procedure call_alarmprogram(3, 102, "add chunk to dbspace failed", v_alarm_text, "");
      end if;
   end if;
end procedure;
autoextend_dbspace accepts 4 arguments:
  • The dbspace you want to check and potentially add a chunk to
  • The minimum number of free KB you want in a dbspace
  • The size in KB of the chunk you want to add if you need to
  • The basename of the chunk that will be added, this will be something like /home/informix/chunks/ROOTDBS and the stored procedure will come up with a unique extension (it is based on the current time) for the chunk like ROOTDBS.20100805.235535
The stored procedure selects the free KB for the dbspace from sysmaster and creates a chunk name, in case it needs it. If the KB free is less than the threshold we will add a chunk through the SQL Admin "add chunk" API. If this is successful we send an event alarm of severity 1 (Information) and if it fails we send an event alarm of severity 2 (Attention).

Modify ALARMPROGRAM to Handle the New Events

This is pretty easy, we just need to modify the case statement in alarmprogram.sh to capture new event types 101 and 102. I'll leave it up to you to do something meaningful (send an email or page) with these alarms.

Add the following lines to the case statement on "$EVENT_CLASS" in alarmprogram.sh before the default of *. For me, this change starts at line 460.
     101)
        # send an email
         ;;
     102)
        # send a page
         ;;

Create a Task in the Scheduler to Run autoextend_dbspace Every So Often

I could run this thing via cron or some other scheduling thingamajigger, but to be honest I don't have much hands on experience with the Scheduler (other than disabling AUS, Art's got me covered with dostats, TYVM) so we're using the Scheduler.

Turns out it is pretty easy to setup a Task within the Scheduler (monitors and sensors may be another story). All you need to do is insert a row into ph_task in the sysadmin database and Informix handles the rest. The schedule you define for a task is pretty flexible. I'm simply going to setup a task to run every hour for each dbspace I want to autoextend. If rootdbs gets below 200 MB autoextend will add a 1 GB chunk and if datadbs01 gets below 1 GB autoextend will add a 4 GB chunk.
database sysadmin;

insert into ph_task (
        tk_name,
        tk_description,
        tk_type,
        tk_group,
        tk_dbs,
        tk_start_time,
        tk_stop_time,
        tk_execute,
        tk_frequency,
        tk_enable
)
values (
        "Autoextend rootdbs",
        "Adds a chunk to rootdbs if needed",
        "TASK",
        "DISK",
        "sysextend",
        datetime(00:00:00) hour to second,
        datetime(23:59:59) hour to second,
        "execute procedure autoextend_dbspace('rootdbs', 204800, 1048576, '/home/informix/chunks/ROOTDBS')",
        interval(1) hour to hour,
        "t"
);

insert into ph_task (
        tk_name,
        tk_description,
        tk_type,
        tk_group,
        tk_dbs,
        tk_start_time,
        tk_stop_time,
        tk_execute,
        tk_frequency,
        tk_enable
)
values (
        "Autoextend datadbs01",
        "Adds a chunk to datadbs01 if needed",
        "TASK",
        "DISK",
        "sysextend",
        datetime(00:00:00) hour to second,
        datetime(23:59:59) hour to second,
        "execute procedure autoextend_dbspace('datadbs01', 1048576, 4194304, '/home/informix/chunks/DATADBS01')",
        interval(1) hour to hour,
        "t"
);

Autoextend in Action

This is kind of something you have to see in action for yourself, but I'll try to capture all of the magic and wonder of autoextend.

Here is some onstat -d output that shows datadbs01 has dropped below 1 GB free. Oh Noes!
IBM Informix Dynamic Server Version 11.50.UC7IE -- On-Line -- Up 10:33:09 -- 1173168 Kbytes

Dbspaces
address  number   flags      fchunk   nchunks  pgsize   flags    owner    name
89342808 1        0x60001    1        1        2048     N  B     informix rootdbs
89342b88 2        0x40001    2        1        2048     N  B     informix llogdbs01
89342ce8 3        0x60001    3        2        2048     N  B     informix datadbs01
 3 active, 2047 maximum

Chunks
address  chunk/dbs     offset     size       free       bpages     flags pathname
89342968 1      1      0          1048576    517819                PO-B- /home/informix/chunks/ROOTDBS.01
89528430 2      2      0          1048576    11                    PO-B- /home/informix/chunks/LLOGDBS01.01
89528608 3      3      0          2097152    29                    PO-B- /home/informix/chunks/DATADBS01.01
895287e0 4      3      0          2097152    433291                PO-B- /home/informix/chunks/DATADBS01.02
 4 active, 32766 maximum

NOTE: The values in the "size" and "free" columns for DBspace chunks are
      displayed in terms of "pgsize" of the DBspace to which they belong.

Expanded chunk capacity mode: always
The Scheduler kicks in and runs autoexend_dbspace which notices datadbs01 has dropped below the threshold, adds a chunk and executes alarmprogram.sh.

Some post autoextend_dbspace onstat -d output:
IBM Informix Dynamic Server Version 11.50.UC7IE -- On-Line -- Up 10:54:32 -- 1173168 Kbytes

Dbspaces
address  number   flags      fchunk   nchunks  pgsize   flags    owner    name
89342808 1        0x60001    1        1        2048     N  B     informix rootdbs
89342b88 2        0x40001    2        1        2048     N  B     informix llogdbs01
89342ce8 3        0x60001    3        3        2048     N  B     informix datadbs01
 3 active, 2047 maximum

Chunks
address  chunk/dbs     offset     size       free       bpages     flags pathname
89342968 1      1      0          1048576    517819                PO-B- /home/informix/chunks/ROOTDBS.01
89528430 2      2      0          1048576    11                    PO-B- /home/informix/chunks/LLOGDBS01.01
89528608 3      3      0          2097152    29                    PO-B- /home/informix/chunks/DATADBS01.01
895287e0 4      3      0          2097152    433291                PO-B- /home/informix/chunks/DATADBS01.02
895289b8 5      3      0          2097152    2097149               PO-B- /home/informix/chunks/DATADBS01.20100805.104832
 5 active, 32766 maximum

NOTE: The values in the "size" and "free" columns for DBspace chunks are
      displayed in terms of "pgsize" of the DBspace to which they belong.

Expanded chunk capacity mode: always
And the following email arrives in my Inbox from alarmprogram.sh
to:andrew@informix-dba.com
from:hal@blogsvr01
subject: Autoextend added a chunk to rootdbs

Arguments sent to alarmprogram.sh

severity  : 2
class     : 101
msg       : chunk added to dbspace
text      : dbspace: datadbs01, chunk path: /home/informix/chunks/DATADBS01.20100805.104832, size: 4194304 KB
file      :

All your base are belong to us,

Hal

7 comments:

  1. Thanks a lot for your help by email, and for this great article.

    ReplyDelete
  2. Dear Ford,

    Does auto extend work with raw space logical volumes (LV) & where we create soft links using ln -s .

    Regards,
    Joseph

    ReplyDelete
  3. Joseph,

    As is, this only works for cooked chunks, but you could probably modify the autoextend_dbspace stored procedure to pull from a pool of reserved raw logical volumes and add them as chunks to a dbspace as needed.

    11.7 includes a feature called Automatic Storage Provisioning that will do what I'm doing here and do it with raw chunks too.

    ReplyDelete
  4. Very cool Andrew,
    you are one smart dude.

    Any idea how best to do a restore from ontape into a SMALLER instance?
    is that possible or do i need to use some type of stdio piping to get the data copied quickly?

    thanks,
    NJ

    ReplyDelete
  5. Thanks NJ!

    I don't think you can reduce the size of your chunks during a restore (there may be some way of doing it but it probably isn't supported), unload/reload is going to be your best bet if you need to reduce the size of your chunks.

    ReplyDelete
  6. Hi Andrew,

    Can we extend the dbspace datadbs chunk while system is operational (busy at the moment)? does it apply the same size to its secondary server? considering DB environment is on HDR. please advise.

    Thanks,
    James

    ReplyDelete