Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
markmumy
Product and Topic Expert
Product and Topic Expert
2,201

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.

5 Comments