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:

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.