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
from wssadmin.sh prompt
to start search administration and next command to remove the old node(s):
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.
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
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
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:
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.
Mattheo - this is awesome. I always wondered why the backup of the homepage-db took 5times as long as the backup of the other dbs. With the procedure in this post, we could reduce the size of the db by 90% (we took all the main Releases since Connections 3...)ReplyDelete
Thank you very much for sharing this!