You can use an event handler to monitor disk space usage and notify you when available space is running low.
The first example in this section is especially useful for monitoring space during loads. You can enable the event handler before you start the load and disable the event handler after the load completes.
-- This event handler sends email to the database -- administrator whenever the IQ main DBSpace is more than -- 95 percent full. -- This event handler runs every minute. The event handler uses -- sp_iqspaceused to sample the space usage. If the space is -- more than 95 percent full, a file that contains the date and -- time is created in the directory where iqsrv15 is -- running. The file contents are then mailed to the database -- administrator and the file is removed. -- This event can be enabled before a load and be used -- to monitor disk space usage during loading. The event can -- then be disabled after the load. create event out_of_space schedule start time '1:00AM' every 1 minutes handler begin declare mt unsigned bigint; declare mu unsigned bigint; declare tt unsigned bigint; declare tu unsigned bigint; call sp_iqspaceused(mt, mu, tt, tu); if mu*100/mt > 95 then call xp_cmdshell('date > ./temp_m_file'); call xp_cmdshell('mailx -s add_main_dbspace iqdba@iqdemo.com < ./temp_m_file'); call xp_cmdshell('/bin/rm -rf ./temp_m_file'); end if; if tu*100/tt > 95 then call xp_cmdshell('date > ./temp_file'); call xp_cmdshell('mailx -s add_temp_dbspace iqdba@iqdemo.com < ./temp_file'); call xp_cmdshell('/bin/rm -rf ./temp_file'); end if; end
The following code creates a timer based event that monitors space usage to help avoid unexpected rollbacks, which may occur in out of space situations on non-privileged operations. The DBSpaceLogger event is created in the sample iqdemo database.
CREATE EVENT DBSpaceLogger SCHEDULE START TIME '00:00:01' EVERY 300 SECONDS HANDLER BEGIN DECLARE DBSpaceName VARCHAR(128); DECLARE Usage SMALLINT; DECLARE cursor_1 CURSOR FOR SELECT DBSpaceName, Usage FROM sp_iqdbspace() WHERE Usage > 0 ORDER BY Usage FOR READ ONLY; OPEN cursor_1; idx1: LOOP FETCH cursor_1 INTO DBSpaceName, Usage; IF SQLCODE <> 0 THEN LEAVE idx1 END IF; IF Usage >= 70 AND Usage < 80 THEN call dbo.sp_iqlogtoiqmsg('Information: DBSpace' + DBSpaceName + '''s usage is more than 70%'); ELSEIF Usage >= 80 AND Usage < 90 THEN call dbo.sp_iqlogtoiqmsg('Warning: DBSpace ' + DBSpaceName + '''s usage is more than 80%'); ELSEIF Usage >= 90 AND Usage < 100 THEN call dbo.sp_iqlogtoiqmsg('Critical Warning: DBSpace ' + DBSpaceName + '''s usage is more than 90%'); END IF; END LOOP; CLOSE cursor_1; END;