Skip to Content

This blog post is written in conjunction with KBA: 2512638

While the KBA provides steps to manually estimate log space consumption during InfoCube compression via data collected by SQL. The aim of this blog is to provide a script that will take the technical name of the InfoCube and perform the calculations in the background.

If the script proves successful we will add it to the KBA.

To estimate the required log space for InfoCube compression we use the following formula:

[ Log Space ] =  [ #inserts * 2 * (30 + avgrowsize) ] + [ #inserts * #num_indexes (2 * (72 + AVGLEAFKEYSIZE)) ]

To obtain this data we need to generate some additional inputs.

  • SID
  • InfoCube ( Technical Name )
  • Object schema
  • Logging database parameters

We firstly look for the SID. We initially take this from the environment variable DB2DBDFT which should be sufficient. However, we also provide opportunity to take from user input with some basic validation.

dbname_input(){
	#Read value for DB2DBDFT into _dbname
	_dbname=$(echo $DB2DBDFT)
	echo -e "\n Press Enter to confirm <SID> or provide new value \n"
	read -e -p "Enter <SID> [$_dbname]:" 
	_dbname=${DBNAME:-$_dbname}
	_dbname_len=$(echo ${#_dbname})
	# Checking <SID> consistency
	if [ $_dbname_len -lt 3 ] ; then
			echo -e "Please enter a valid 3-Character <SID> \n"
			dbname_input
	else
			return 1
	fi
}

Next we ask for the technical name of the InfoCube from the user. Once provided we then:

  1. Convert to uppercase. This is for syntax reason for when we are calling SQL later
  2. Check if the InfoCube provided is a valid InfoCube with a quick query for its F fact table in syscat.tables
infocube_input(){
	#Read in technical name of InfoCube from user input
	read -e -p "Enter name of INFOCUBE:" _infocube
	#Convert to uppercase for later SQL
	_infocube=$(echo "$_infocube" | tr '[:lower:]' '[:upper:]')
	#Check if it is a valid infocube
	_infocube_valid=$(db2 -x "select count(*) from syscat.tables where tabname = '/BIC/F${_infocube}'")
	if [ $_infocube_valid = 0 ] ; then
			echo -e "Please enter a valid INFOCUBE \n"
			infocube_input
	else
			#Obtain schema and return to main
			_infocube_schema=$(db2 -x "select tabschema from syscat.tables where tabname = '/BIC/F${_infocube}'")
			return 1
	fi
}

Then we look to calculate existing log space that is available to the database. We do this by taking the number of logs and log file size from the database parameters and performing a simple calulation of:

( LOGPRIMARY + LOGSECOND ) * LOGFILSIZ

Since LOGFILSIZ is represented in 4 KB pages we then multiply this by 4 to get the total KB log space.

calculate_logspace(){
	#Read in logging parameters from dbcfg
	_logprimary=$(db2 -x "select value from sysibmadm.dbcfg where name ='logprimary'")
	_logsecond=$(db2 -x "select value from sysibmadm.dbcfg where name ='logsecond'")
	_logfilsiz=$(db2 -x "select value from sysibmadm.dbcfg where name ='logfilsiz'")
	#Calculate total available log sapce
	_num_logs=$((_logprimary + _logsecond))
	_total_logspace_KB=$((_num_logs * ( _logfilsiz * 4 )))
	_total_logspace_MB=$((_total_logspace_KB/1024))
	echo -e "\n Total available logspace:  $_total_logspace_MB MB \n"
}

Once we have all this data we can perform the SQL required to solve the formula:

[ Log Space ] =  [ #inserts * 2 * (30 + avgrowsize) ] + [ #inserts * #num_indexes (2 * (72 + AVGLEAFKEYSIZE)) ]
calculate_comp_logspace(){
	_num_inserts=$(db2 -x "select count(*) from ${_infocube_schema}.\"/BIC/F${_infocube}\" group by KEY_${_infocube}P order by KEY_${_infocube}P asc fetch first 1 rows only")
	_data_part=$(db2 -x "select 2 * (20 + avgrowsize) as data_part from syscat.tables where tabname = '/BIC/F${_infocube}'")
	_index_part=$(db2 -x "select sum(2 * (72 + AVGLEAFKEYSIZE)) as index_part from syscat.indexes where tabname = '/BIC/E${_infocube}'")
	_logspace_req=$(( _num_inserts * ( _data_part + _index_part )))
	_logspace_req=$((_logspace_req/1024/1024))
	echo -e "Total logspace required for infocube compression of ${_infocube} is: $_logspace_req MB \n"
}

Complete script is available on the following share

DB6_InfoCube_Compression

Running the script:

  1. Download the ‘infocube_comp_log_space.sh’ from the link above
  2. It may be necessary to change permissions on the script so that the db2<sid> user can execute:
    • chmod 0755 infocube_comp_log_space.sh
  3. Run the script as the db2<sid> user
    • ./infocube_comp_log_space.sh

Once executed the script will request:

  1. Confirmation of SID
  2. Technical name of InfoCube

This can be seen in action below.

As you can see the script then returns the current log space and the estimated log space required for InfoCube compression. You can then determine whether there is sufficient log space for this operation. Remember, that this only estimates the log space for this single application and does not factor in any other log consumption that may be present on the system during InfoCube compression.

Any feedback or change requests are greatly appreciated.

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. Former Member

    Hello,

    I don’t understand how the formula works, I tried with one INFOCUBE :

     

     

    Enter name of INFOCUBE:

    FC2C01

    Total available logspace:  92137 MB

    Total logspace required for infocube compression of FC2C01 is: 291 MB

     

    and it estimate that I need only 291 MB redo logs… but the INFOCUBE contains 24 millions rows…

    Regards,

    Damien.

    (0) 
    1. David Godson Post author

      Hi Damien,

      Can you update with the outputs of the following:

      select key_<INFOCUBE>p, count(*) from “/BIC/F<INFOCUBE>” group by key_<INFOCUBE>p
      select count(*) from syscat.indexes where tabname = ‘/BIC/F<INFOCUBE>’
      select count(*) from syscat.indexes where tabname = ‘/BIC/E<INFOCUBE>’
      select AVGLEAFKEYSIZE from syscat.indexes where tabname = ‘/BIC/F<INFOCUBE>’
      select avgrowsize from syscat.tables where tabname = ‘/BIC/F<INFOCUBE>’

      For example with ‘FC2C01’ this would be:

      select key_FC2C01p, count(*) from “/BIC/FFC2C01” group by key_FC2C01p

      Also can you update with a fresh run of the compression script so that we have latest data from all outputs.

      Thanks,

      David

      (0) 
  2. Former Member

    Hello David,

    As it is not containing sensitive date I can send you everything.

    I ran again your script :

    ./infocube_comp_log_space.sh
    \n Press Enter to confirm <SID> or provide new value \n
    Enter <SID> [FW1]:
    Enter name of INFOCUBE:FC2C01
    \n Total available logspace:  92137 MB \n
    Total logspace required for infocube compression of FC2C01 is: 498 MB \n

     

    The SQLs :

    select key_FC2C01p, count(*) from “/BIC/FFC2C01” group by key_FC2C01p;

    KEY_FC2C01P	2
    4784	24311719
    4786	11884
    4788	146002
    4790	231095
    4792	190065
    4794	155465
    4796	187187
    4798	61403
    4800	24596
    ...

     

    select count(*) from syscat.indexes where tabname = ‘/BIC/FFC2C01’;

    15

    select count(*) from syscat.indexes where tabname = ‘/BIC/EFC2C01’;

    15

     

    select AVGLEAFKEYSIZE from syscat.indexes where tabname = ‘/BIC/FFC2C01’;

    AVGLEAFKEYSIZE
    4
    4
    4
    4
    4
    4
    4
    4
    4
    4
    4
    4
    4
    4
    4

     

    select avgrowsize from syscat.tables where tabname = ‘/BIC/FFC2C01’;

    240

     

    Regards,

    Damien.

     

    (0) 
    1. David Godson Post author

      Hi Damien,

      Thanks for this, would it be possible for you to open an incident referencing this KBA ? We could then check directly on the system. Please reference my name and I will take over the incident.

      Thanks again,

      David

      (0) 
  3. Jürgen Noe

    Hi all,

    I wrote a little ABAP to check the spaces using EXEC SQL:

    REPORT ZRE_GLB_DBSETTINGS.
    data: l_tabschema type string.
    data :l_primary type i.
    data: l_second type i.
    data: l_filesize type i.
    data :l_totalspace type i.
    data: l_inserts type i.
    data: l_datapart type i.
    data: l_indexpart type i.
    data :l_reqspace type f.
    data :l_reqspace_mb type i.

    EXEC SQL.
    select tabschema into :l_tabschema
    from syscat.tables
    where tabname = ‘/BIC/FTSTCB’
    endexec.

    EXEC SQL.
    select value into :l_primary
    from sysibmadm.dbcfg where name =’logprimary’
    endexec.

    EXEC SQL.
    select value into :l_second from sysibmadm.dbcfg
    where name = ‘logsecond’
    endexec.

    EXEC SQL.
    select value into :l_filesize from sysibmadm.dbcfg
    where name = ‘logfilsiz’
    endexec.

    l_totalspace = ( l_primary + l_second ) * l_filesize * 4 / 1024.

    write: ‘Total available Logspace: ‘, l_totalspace, ‘ MB’.
    NEW-LINE.

    exec sql.
    select count(*) into :l_inserts from SAPBWE.”/BIC/FTSTCB” group by KEY_TSTCBP
    order by KEY_TSTCBP
    endexec.

    write: ‘Number of necessary inserts:’, l_inserts.
    NEW-LINE.

    exec sql.
    select 2 * (20 + avgrowsize) as data_part into :l_datapart from syscat.tables where tabname = ‘/BIC/FTSTCB’
    endexec.
    write: ‘Number of data parts:’, l_datapart.
    NEW-LINE.

    exec sql.
    select sum(2 * (72 + AVGLEAFKEYSIZE)) as index_part into :l_indexpart from syscat.indexes where tabname = ‘/BIC/FTSTCB’
    endexec.
    write: ‘Number of index parts:’, l_indexpart.
    NEW-LINE.

    l_reqspace = l_inserts * ( L_datapart + l_indexpart ) / 1024 / 1024.
    l_reqspace_mb = l_reqspace.
    write: ‘Required Logspace: ‘, l_reqspace_mb, ‘ MB’.

     

    Feel free to use if you like it.

    (0) 

Leave a Reply