###### Technical Articles

# DB6: Script to estimate log space required for InfoCube Compression

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:

- Convert to uppercase. This is for syntax reason for when we are calling SQL later
- 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

Running the script:

- Download the ‘infocube_comp_log_space.sh’ from the link above
- 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

- Run the script as the db2<sid> user
- ./infocube_comp_log_space.sh

Once executed the script will request:

- Confirmation of SID
- 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.

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.

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

Hello David,

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

I ran again your script :

The SQLs :

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

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';

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

240

Regards,

Damien.

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

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.