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
- 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
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
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: alwaysThe 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: alwaysAnd the following email arrives in my Inbox from alarmprogram.sh
to:firstname.lastname@example.org 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