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 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). This procedure is a lightweight procedure that captures the size 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 procedure output:
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 9.76G 200M 1 1 T 1M 1H,255968F,32D,128M,36B N (NULL) T
IQ_SYSTEM_TEMP TEMPORARY T T 1 9.76G 400M 2 2 T 1M 2H,96F,32A,16I N (NULL) T
user_main MAIN T T 7 97.7G 400M 2 2 T 1M 2H,777474A N (NULL) T
(3 rows)
Notice that "user_main" has a size of 97.7G and a usage of 7 (7%). The dbspace is roughly (97.7GB *1024 *1024) * 7% or 7,171,211.264 KB. Not precise, but close enough for system planning. The point is that I wanted a lightweight procedure and I wanted to avoid reverse engineering SAP IQ system procedures in the event.
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 unsigned bigint
) on SYSTEM;
create table IQ_DBSize (
capture_timestamp datetime
, database_sizeKB unsigned bigint
, database_usedKB unsigned bigint
) on SYSTEM;
drop event if exists capture_size;
Now we can create the event:
create event capture_size
SCHEDULE size_schedule
START TIME '00:00 AM' EVERY 24 HOURS
HANDLER
begin
declare _ServerType char(1);
declare _ServerCnt unsigned bigint;
declare _ServerCoordCnt unsigned bigint;
declare _dbsize unsigned bigint;
declare _dbused unsigned bigint;
-- 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.
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' );
-- 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
end;
I changed the event timer to run "EVERY 1 MINUTES" so that I could show how the procedure will output data to the tables.
In SAP IQ 15.1 SP05 after 3 iterations, this event output looks like this:
select * from IQ_DBSize;
capture_timestamp database_sizeKB database_usedKB
----------------------------------------------------------
2021-11-19 14:29:50.099509 112640000 8269128
2021-11-19 14:30:00.016558 112640000 8269128
2021-11-19 14:31:00.01503 112640000 8269128
(3 rows)
select * from IQ_DBSpaceSize;
capture_timestamp dbspace_name dbspace_sizeKB
--------------------------------------------------------
2021-11-19 14:29:50.087938 IQ_SYSTEM_MAIN 2149161
2021-11-19 14:29:50.087938 user_main 7171211
2021-11-19 14:30:00.007595 IQ_SYSTEM_MAIN 2149161
2021-11-19 14:30:00.007595 user_main 7171211
2021-11-19 14:31:00.005074 IQ_SYSTEM_MAIN 2149161
2021-11-19 14:31:00.005074 user_main 7171211
(6 rows)
That same event in SAP HANA Cloud, data lake, the output looks like this:
select * from IQ_DBSize;
capture_timestamp database_sizeKB database_usedKB
----------------------------------------------------------
2021-11-19 14:30:00.54169 96503070720 1971885120
2021-11-19 14:31:00.237194 96503070720 1971879456
2021-11-19 14:32:00.236421 96503070720 1971886752
(3 rows)
select * from IQ_DBSpaceSize;
capture_timestamp dbspace_name dbspace_sizeKB
--------------------------------------------------------
2021-11-19 14:30:00.039703 IQ_SYSTEM_MAIN 252544077
2021-11-19 14:30:00.039703 user_main 1906965479
2021-11-19 14:31:00.030404 IQ_SYSTEM_MAIN 252544077
2021-11-19 14:31:00.030404 user_main 1906965479
2021-11-19 14:32:00.032755 IQ_SYSTEM_MAIN 252544077
2021-11-19 14:32:00.032755 user_main 1906965479
(6 rows)
Please comment if there are features of this that you want or if you find any issues with it.
