Last week another client told me that their Sterling B2B Integrator’s DB had been growing uncontrollably. I checked the DataBase Usage section and found that there are too many records to be purged – it meant that something was wrong with the Purge service. When it comes to the purge service, there are not too many options – I immediately checked the Locks and found that there had been 2 locks for about a month. Something went wrong, the Purge service setup these locks but was not able to release them. We unchecked them and 24 hours later it got back to the normal routine. Now it’s time to rebuild the indexes and shrink tables…
If you are working for and international company which operates 24×7, you might need to find a quiet period of time when SI/GIS server is not processing a lot of documents (for example, for running heavy BackupService or do any kind of maintenance and so on). I use the following SQL queries to gather some usage stats:
Per day hours:
select count(*) as CNT, TO_CHAR(start_time, 'HH24') as DT from WF_INST_S --where TO_CHAR(start_time, 'yyyy') = '2014' group by TO_CHAR(start_time, 'HH24') order by TO_CHAR(start_time, 'HH24')
Per week days:
select count(*) as CNT, TO_CHAR(start_time, 'D') as WeekDay from WF_INST_S --where TO_CHAR(start_time, 'yyyy') = '2014' group by TO_CHAR(start_time, 'D') order by TO_CHAR(start_time, 'D')
As I wrote in my previous post, there were some problems with the BackupService and it was turned off for about 2 months. The DB has grown up dramatically and we’ve started having problems with some BPs. So, we had to re-build a couple of indexes in our GIS DB (Oracle) as it described here SQL queries taking 100% CPU in the database. It solved our problems perfectly.
01 May 2014: There have been some changes on the IBM portal and it looks it doesn’t work anymore – all the links are broken
02 Jul 2014: The blog with this article has been deleted. As I remember, one of 2 options were supposed to do:
1. Rebuild indexes on CORRELATION_SET
2. On CORRELATION_SET table – drop index SCI_IDX_57 and create new one with column (OBJECT_ID, KEY_ID, VALUE_UPPER)
One of our clients had some problems with the Backup Service in their GIS system. They turned it off for about 2 months and at the end of this period there were about 4M records with ARCHIVE_FLAG = 0 in the ARCHIVE_INFO table. The DB size had increased to hundreds of GBs. I decided to change all of the records with ARCHIVE_FLAG = 0 to ARCHIVE_FLAG = 2 and ARCHIVE_DATE = “now() + 1 year” to make them “invisible” for both the Backup and Purge Services. Then I turned the Backup Service back (thus I stopped the DB growing) and started “returning” those changed records back day by day. So, the index/backup/purge services got back to the rut.
Important: it’s just an idea, not the direct instructions. You should understand how it works to make such changes… If you don’t understand the SI/GIS DB you mustn’t do anything with it except maybe select queries.
Here is the SQL query I used (I ran it several times, day by day to avoid excessive DB loading):
update ARCHIVE_INFO set ARCHIVE_FLAG = 2, ARCHIVE_DATE = ARCHIVE_DATE + 365 where TO_CHAR(ARCHIVE_DATE, 'yyyy-mm-dd') = '2014-01-01' and ARCHIVE_FLAG = 0; commit;