Out of dbspace handling

Sybase IQ 15.0 introduces a new feature so that the IQ server no longer waits for additional space on an out-of-dbspace condition, but instead rolls back either the entire transaction or rolls back to a savepoint. If there is not enough temporary or main dbspace available for a buffer or dbspace allocation request, then the statement making the request rolls back. At this point, the DBA can add more space to a dbspace using the ALTER DBSPACE or the ALTER FILE command.

If the entire transaction is rolled back on an out-of-dbspace condition, the following new error message is reported:

"%1 -- Transaction rolled back"

Probable cause: This transaction has been rolled back because the server encountered an error during a critical operation.

If a buffer or dbspace allocation request fails because there is no space in the dbspace, the following new error message is logged in the iqmsg message file:

"You have run out of space in %2 DBSpace. %1"

This error message replaces the error message "You have run out of { IQ STORE | IQ TEMPORARY STORE } dbspace in database <dbname>. In another session, please issue a CREATE DBSPACE ... { IQ STORE | IQ TEMPORARY STORE } command and add a dbspace of at least nn MB."

If a buffer allocation request finds a dirty buffer, but the buffer manager cannot flush the buffer due to an out-of-space condition, the following new error message is returned and the current statement rolls back:

"%2: All buffer cache pages are in use, ask your DBA to increase the size of the buffer cache. %1"

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.

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; 

The DBSpaceLogger event is created in the sample iqdemo database.