IBM DB2 for IBM Connections 4.5 / 5.0 – Best practises
this time I want to share some thoughts about different DB2 (DB and DBM cfg) parameters of IBM DB2 software that you could use when setting up an IBM Connections 4.5 or 5.0 environment. This guarantees that everything will run smoothly with less interactions for a database administrator.
Running Reorg und Runstat statements
It is recommended (during a maintenance window) to run the reorg und runstats scripts on each database.
Therefore you need to connect to your database as instance owner and run the scripts provided in the Wizard package of IBM Connections (e.g. for “Blogs” Application)
db2 -td@ -vf /ibm/Connections/connections.sql/blogs/db2/reorg.sql
db2 -td@ -vf /ibm/Connections/connections.sql/blogs/db2/runstats.sql
The reorg.sql file consists of commands like
The reorg operation is similar to a defragmentation of a hard disc. It frees empty spaces in pages and eventually it reorganizes data according to an index. Depending on the features it creates the compression dictionary and compresses data.
The runstats.sql file consists of commands like
Runstats collects statistical information about data in tables and indexes. You can view this information in views in the SYSSTAT schema. SYSCAT tables have some of the data as well. In my opinion you should run those commands when auto_runstats is not activated in your environment.
Separation of DB2Diag file
It`s IBM Connections best practice to separate all databases into own instances (up to 15 instances on 1 server).
Furthemore it is DB2 best practice to change the DB2 diaglog size, so that you do not generate log files with several GBs of size. The location of DB2s log files can be found in the dbm configuration. Check the value using:
There are two major error logging files. The db2diag.log is called the DB2 diagnostic log and it is written to based on the value of DIAGLEVEL. The other main file is called the administration notification log, and is named <instance_name>.nfy. Other files can be written to the DIAGPATH as well.
Therefore it is highly recommended to limit this value to 5-10 MB. With this the DB2 Admin can ensure that the file won’t get too big.
If it reaches the limit of the diagsize DB2 will create another file and continue writing entries.
Troubleshooting will get easier here. An important fact that you should not forget 😉
Configuration of IBM Connections Backups
In order to backup IBM Connections without stopping the environment it is mandatory to change the database configuration to allow online backups. We run those online backups every day at night with the compress option . Through this, backup files get lower in size.
Additionaly you have to set the paramater for the db cfg parameter logarchmeth1 like this
But before you can create Online Backups you have to create one offline backup in order to activate archiving of logs.
Depending on your customer requirements you have to make a decision how long you want to keep backups. You might use the following parameters:
In this case DB2 will remove old backups that are running out of the specified intervals.
Activating the automated compression of archived log files
Another cool thing of optimizing your DB2 V10.1 environment is the logarchcompr1 option.
If you enable this you will save disk space and some administrative tasks.
To find out whether your database has this option enabled, enter the following command:
This option can be enabled with the command:
Feedback, experiences and some Input from your site is very welcome 🙂