Technical Blog Post
First activation of database is taking too long to complete on Windows Platform.
Sharing a latest interesting scenario we debugged in the lab.
User reported that after server reboot, whenever he first attempt activating the database, it takes more than 20+mins and consecutive activation does not take that long time. This is DB2 Instance on Windows.
From db2trc data analysis we saw sqloopenp() calls for the same db2 file sometimes took more than 10 sec to complete and sometime they complete right away. Underneath sqloopenp() we called 'Createfile()' from the Windows API and that's where the slowness was coming from. So we use Microsoft's Process Monitor - advanced monitoring tool to debug this issue further (https://technet.microsoft.com/en-us/sysinternals/processmonitor.aspx)
We set filters (which is a setting in Process Monitor) on those specific db2 files where we were having intermittent slowness and also on other file where sqloopenp() returned in expected time. Idea was to do comparative analysis of slowness noted for the good and bad case files.
From the Process Monitor logfile we found that for each open from 'db2sysc.exe' triggers 'coreServiceShell.exe' to perform similar calls.
Somehow each failure, whatever the reason is, takes up to 6 seconds to resolve as can be seen below:
3:16:40.9863474 coreServiceShell.exe 1620 CreateFile SQLOGCTL.LFH.1 ACCESS DENIED (6.0359416)
3:16:47.0224845 coreServiceShell.exe 1620 QueryOpen SQLOGCTL.LFH.1 SUCCESS (0.0000123)
3:16:47.0268871 coreServiceShell.exe 1620 CreateFile SQLOGCTL.LFH.1 ACCESS DENIED (6.0327189)
3:16:53.0598300 coreServiceShell.exe 1620 QueryOpen SQLOGCTL.LFH.1 SUCCESS (0.0000318)
This proved that the culprit was outside DB2. So the coreServiceShell.exe is problematic process which was interfering here. The process known as Trend Micro Anti-Malware Solution Platform belongs to software Trend Micro Titanium or Trend Micro™ Titanium™ Maximum or Trend Micro Titanium Internet Security or Trend Micro™ Titanium™ Maximum Security or TELUS security services or Virgin Media Security by Trend Micro (www.trendmicro.com). After user uninstalled the unnecessary Trend Micro anti-virus the database activation completed instantly.