Let us note, first, that this blog and code applies to both SAP HANA Cloud, data lake (cloud IQ) and SAP IQ (on-premise IQ).
For many SAP IQ use cases, customers have custom written scripts and various jobs to capture information about SAP IQ, include the database size and dbspace sizes over time. In certain SAP application use cases like Near Line Storage for SAP BW and SAP BW4 DTO as well as Information Lifecycle Management for SAP ERP, we have relied upon front ends that have been replaced and that functionality has been lost.
It is also worth noting that SAP HANA Cloud, data lake does not yet have a feature to capture the database and dbspace size over time.
This event was written to handle both use cases.
I would caution that we want to careful about what code you put in the event and the frequency with which it runs. Some functions to capture space usage are more heavyweight than others. The higher the frequency of those procedure calls, the higher the chance of causing an inadvertent performance bottleneck. For more details, see SAP Note 1965414.
I have written a rather simple event (code below) that will capture database information once a day, at midnight. This is configurable to meet your business and reporting needs. The event captures both the entire database size as well as the granular size of each main dbspace.
The overall database size is captured by using the SAP IQ procedure sp_iqspaceused (SAP IQ 16.1 SP05 manual). This procedure outputs the sum total of all main dbspaces, include IQ_SYSTEM_MAIN. This data is captured in a table stored in the catalog store (SYSTEM) called IQ_DBSize.
To capture the size of each dbspace, I have used the procedure sp_iqdbspace(SAP IQ 16.1 SP05 manual) and sp_iqclouddbspace (SAP HANA Cloud, data lake relational engine manual). The procedures will capture the sizes of each dbspace in the system. The downside is that it does not report blocks in use, but rather a usage percentage and a human readable total size. While not 100% accurate, I use these values to compute rough size for each dbspace. See this example of the output (when run on the coordinator):
call sp_iqdbspace();
DBSpaceName DBSpaceType Writable Online Usage TotalSize Reserve NumFiles NumRWFiles Stripingon StripeSize BlkTypes OkToDrop lsname is_dbspace_preallocated
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IQ_SYSTEM_MAIN MAIN T T 21 256G 768G 1 1 T 1M 1H,1677696F,32D,250A,114O,630X,128M,37B,32C N (NULL) T
IQ_SYSTEM_TEMP TEMPORARY F T 0 5G 0B 1 0 T 1M N (NULL) T
hotsql_dbspace MAIN T T 4 512M 0B 1 1 T 1M 1H,514A N (NULL) T
user_object_store MAIN T T NA NA 0B 1 1 F 1M 1H Y (NULL) T
(4 rows)
call sp_iqclouddbspace();
DbspaceName DbspaceId CurrSizeByte MaxSizeByte PageSizeByte
-----------------------------------------------------------------
user_object_store 16388 5242880000 0 262144
(1 rows)
The output from sp_iqdbspace is used to capture spaces other than the default user main dbspace. sp_iqclouddbspace is used to capture the space usage of the user main dbspace, user_object_store. Is SAP IQ, sp_iqdbspace is all that is needed. Both variants are included in the code below.
It is important to note that the code below must all run on the coordinator or a simplex system. The coordinator in an SAP IQ Multiplex or on SAP HANA Cloud, data lake relational engine is the only server that maintains the full list of blocks allocated and in use. Consequently, sp_iqdbspace will return "NA" values for the dbspaces unless run on the coordinator. Additionally, the CREATE TABLE statements use the catalog system space for storing the results. These tables are allocated and managed on a per node basis. The coordinator is always available and guaranteed to have proper resources to manage this data.
First, let's make sure that the event doesn't exist and create the SYSTEM tables:
drop table if exists IQ_DBSpaceSize;
drop table if exists IQ_DBSize;
create table IQ_DBSpaceSize (
capture_timestamp datetime
, dbspace_name varchar(255)
, dbspace_sizeKB bigint
) on SYSTEM;
create table IQ_DBSize (
capture_timestamp datetime
, database_sizeKB bigint
, database_usedKB bigint
) on SYSTEM;
drop procedure if exists sp_capture_size_event;
drop event if exists capture_size;
Now we can create the event:
create procedure sp_capture_size_event()
begin
declare _IsHDLRE char(1);
declare _ServerType char(1);
declare _ServerCnt unsigned bigint;
declare _ServerCoordCnt unsigned bigint;
declare _dbsize unsigned bigint;
declare _dbused unsigned bigint;
-- is this an IQ system or HDLRE?
select count(*) into _IsHDLRE from sysprocedure where lower( proc_name ) = 'sp_iqclouddbspace';
-- use sp_iqmpxinfo (works in IQ and HDL) to know if this is simplex or the coordination on MPX
select count(*) into _ServerCnt from sp_iqmpxinfo();
select count(*) into _ServerCoordCnt from sp_iqmpxinfo()
where server_name = @@servername and role = 'coordinator';
if _ServerCnt = 0 then
set _ServerType = 's';
elseif _ServerCoordCnt = 1 then
set _ServerType = 'c';
else
set _ServerType = 'o';
end if;
if lcase(_ServerType) = 's' or lcase(_ServerType) = 'c'
then
call dbo.sp_iqlogtoiqmsg( 'DBSIZE: can run on this server type: '|| _ServerType );
-- capture dbspace usage
-- the size is a rough number since it is a reverse of the usage, a percentage,
-- and the size, which was converted to human readable format.
-- this was easier than reverse engineering all of sp_iqdbspace.
if _IsHDLRE = 1
then
call dbo.sp_iqlogtoiqmsg( 'DBSIZE: capturing HDLRE cloud size: '|| _ServerType );
insert into IQ_DBSpaceSize
select
getdate()
, dbspacename
, CurrSizeByte / 1024
from sp_iqclouddbspace() ;
-- exclude any dbspaces you don't want to see in the above line
-- hotsql_dbspace is an HDL reserved dbspace and shouldn't be captured.
end if;
insert into IQ_DBSpaceSize
select
getdate()
, dbspacename
, ( usage / 100 ) * case lower ( right( totalsize, 1 ) )
when 'k' then replace( lower( totalsize ), 'k','')
when 'm' then replace( lower( totalsize ), 'm','') * 1024
when 'g' then replace( lower( totalsize ), 'g','') * 1024 * 1024
when 't' then replace( lower( totalsize ), 't','') * 1024 * 1024 * 1024
when 'p' then replace( lower( totalsize ), 'p','') * 1024 * 1024 * 1024 * 1024
end
from sp_iqdbspace() where lower( dbspacetype ) = 'main'
and dbspacename not in ( 'hotsql_dbspace' ) and usage <> 'NA';
-- exclude any dbspaces you don't want to see in the above line
-- hotsql_dbspace is an HDL reserved dbspace and shouldn't be captured.
-- capture overall MAIN STORE (user and system) usage and store that
call sp_iqspaceused ( _dbsize, _dbused, null, null, null, null, null, null, null, null );
call dbo.sp_iqlogtoiqmsg( 'DBSIZE: '|| getdate()||' '|| _dbsize||' '|| _dbused );
insert into IQ_DBSize values( getdate(), _dbsize, _dbused );
commit;
return;
else
call dbo.sp_iqlogtoiqmsg( 'DBSIZE: cannot run on this server of type: '|| _ServerType );
end if;
commit;
end;
create event capture_size
SCHEDULE size_schedule
START TIME '00:00 AM' EVERY 24 hours
HANDLER
begin
call sp_capture_size_event();
commit;
end;
In SAP HANA Cloud, data lake relational engine this event output looks like this:
select * from IQ_DBSize;
capture_timestamp dbspace_name dbspace_sizeKB
-----------------------------------------------------------
2025-04-25 19:33:51.719534 user_object_store 5120000
2025-04-25 19:33:51.723851 IQ_SYSTEM_MAIN 56371445
(2 rows)
select * from IQ_DBSpaceSize;
capture_timestamp database_sizeKB database_usedKB
----------------------------------------------------------
2025-04-25 19:33:51.754387 268959744 53733920
(1 rows)
In SAP IQ 16.1 SP05 the output looks like this:
select * from IQ_DBSize;
capture_timestamp dbspace_name dbspace_sizeKB
--------------------------------------------------------
2025-04-25 19:41:34.124462 IQ_SYSTEM_MAIN 1074580
2025-04-25 19:41:34.124462 user_main 1535115
(2 rows)
select * from IQ_DBSpaceSize;
capture_timestamp database_sizeKB database_usedKB
----------------------------------------------------------
2025-04-25 19:41:34.133558 56320000 2378928
(1 rows)
As this procedure runs, daily by default, more data will be captures so that you can trend the space consumption over time.
Please comment if there are features of this that you want or if you find any issues with it.
