IBM Connections : If the storage on your database server is growing fast , read this post and check the HOMEPAGE DB

During last weeks I was working on a Customer environment where I was the DB2 server was running out of space.

After some investigations I saw  HOMEPAGE DB size was huge compared to others DBs ( about 50 GB of DB size allocation and the compress backup was around 15GB while others DBs compressed backup was around 1 GB).

After more investigations I've realized most of the space was handled by the table named HOMEPAGE.SR_INDEX_DOCS and this pointed me to this technote.

In few words If the environment is build from a migrations of previous version (and this was born 2 or 3 Connections release ago.. )  and during the years the name of the indexing node has changed (and in this case it was.. )  the previous indexing node could be still referenced inside this TABLE causing the growing of the Table to an uncontrolled and unjustified size.

The fist step is check the node inside the HOMEPAGE DB using the following select:

SELECT * FROM HOMEPAGE.SR_INDEX_MANAGEMENT

DB2 will reply with the list of the indexing node. If old unused node are currently listed  ( check  this technote) you have to remove this entry from the db with following steps:

from  wssadmin.sh prompt

execfile("searchAdmin.py")

to start search administration and next command to remove the old node(s):

SearchService.removeIndexingNode("Node01:cluster1_server1") 

If the server is removed correctly the server will reply '1'  .

After old node deletion , document contained inside the table will start to decrease.
To check this document deletion you could use the following select

SELECT COUNT(*) FROM HOMEPAGE.SR_INDEX_DOCS

when the document number will be near 0 you could reduce the TABLE using the script provided by IBM shipped with the DB Wizard.

Inside WizardHome/connections.sql/homepage/db2

run the following commands

(not required but suggested)

db2 -td@ -vf  reorg.sql

db2 -td@ -vf  updateStats.sql

now the script that will perform the truncate of the Table

db2 -td@ -vf clearSearchIndexTables.sql   

To recover the space inside the DB2 partition we need now to resize the Tablespace that owns the table, in this case HOMEPAGETABSPACE

As first step we need to prepare the tablespace to recover space with this command

ALTER TABLESPACE TS LOWER HIGH WATER MARK

next run the following select to see how many MB you could reclaim inside that TABLESPACE

select tbsp_name, tbsp_free_pages, tbsp_page_size * tbsp_free_pages /1024 / 1024 AS tbsp_reclaimable_mb from table(MON_GET_TABLESPACE('HOMEPAGETABSPACE', NULL))

the reply of this command will be like  XXX MB and we have to use this result as argument of the next command:

ALTER TABLESPACE HOMEPAGETABSPACE REDUCE (ALL CONTAINERS XXX M) 

after this, we  have successfully recovered the space!

If you are in this situation you could recover several GB considering the DB allocation space and the backups so if you have any doubt regard this setting I suggest to check your environment !

This commands was run in a linux environment with DB2 and I was same issues on IBM Connections  5.5 and  6.0 .

NB:  When you are running the truncate script or the alter table Connections should be SHUTDOWN and I strongly suggest you to perform one or more backup of the Homepage DB during the steps !!


P.S  I like to thanks my friends from IBM Connections Space on Watson Workspace that helped me during the debug of this issue and Victor for his slide that helped me on DB2 part.