Posted by It is no secret that I’ve been doing more work with DB2 on Windows lately. I know a number of clients who are looking for DBAs with experience with DB2 on Windows and are frustrated that they cannot find more candidates. Truthfully, it is not a steep learning curve to work with DB2 on Windows when you’re used to DB2 on Linux or UNIX, but one of the more painful areas is scripting. Unless you’re lucky enough to have always scripted in an OS-agnostic Perl and are able to get Perl installed on all your Windows servers, scripting is a difficult area of conversion. Those of us who are familiar with ksh or bash often use small scripts just from the command line, and can whip up other scripts quickly as needed. Batch is still a bit of a mystery to me. It never works quite how I think it should. I am a PowerShell fan, and have written a number of scripts in it at this point. I cannot say I have fully embraced the object-orientation that PowerShell enables, but at least I have a language that makes sense to me. I thought I’d share a script that is particularly useful.
Please note that even more than usual, this script is for use or re-writing at your own risk. It may or may not work for you and it may or may not cause issues on your system. Please test it or anything derived from it thoroughly, and use it only at your own risk.
Objective
The purpose here is to detect when DB2 starts and then activate all local databases. This is useful for standby HADR servers and also for any database environment where the local databases should be activated.
The advantage of explicit database activation instead of just allowing connections to implicitly activate databases is mostly the following:
- Overhead – Each database activation takes resources and time. For one thing, the transaction log files must be allocated at activation time. Depending on the size and I/O characteristics, database activation can take quite a bit of time. If this activation is allowed to implicitly happen on connection, a user may be waiting that time.
- Monitoring – Many of the metrics DBAs look at when analyzing a database are since the last activation. If that was 5 minutes ago, there isn’t much useful information.
It is easy to see in the db2 diagnostic log if activation and deactivation are happening frequently. A first connection to a database that has not been activated looks like this:
2016-08-25-19.19.22.357112-360 E2848707E513 LEVEL: Event PID : 10154 TID : 139748811204352 PROC : db2sysc INSTANCE: db2inst1 NODE : 000 DB : SAMPLE APPHDL : 0-16 APPID: *LOCAL.db2inst1.160826011922 AUTHID : DB2INST1 HOSTNAME: ecrooks-VirtualBox EDUID : 18 EDUNAME: db2agent (SAMPLE) FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase::FirstConnect, probe:1000 START : DATABASE: SAMPLE : ACTIVATED: NO
You can therefore use a nifty db2diag command to see how many times within the last day (or whatever time period you want to look at) the database has been activated by a connection:
$ db2diag -H 1d -g function:=FirstConnect,startevent:=NO -fmt "%ts %db" 2016-08-25-19.14.17.420028 SAMPLE 2016-08-25-19.14.56.361221 SAMPLE 2016-08-25-19.16.55.088154 SAMPLE 2016-08-25-19.18.42.565495 SAMPLE 2016-08-25-19.19.22.357112 SAMPLE
This will list the timestamp and database name of each first-connection, implicit activation of local databases. In this case, within the last day. If you’re seeing multiple activations a day, it is fairly likely that explicit activation may be beneficial.
Script for Activation on Windows
Below is the script I’ve used successfully to detect a db2start, no matter what source it comes from and explicitly activate each database. This assumes the default copy of DB2 on the Windows server is used. This must be scheduled for each DB2 instance, if there is more than one.
# Set the DB2 instance name
$db2instance="DB2_01-0"
# Define log file
$out_file="C:\xtivia\activate_log.txt"
# intialize the DB2 command line enviornment
set-item -path env:DB2CLP -value "**$$**"
# set the DB2 instance
set-item -path env:DB2INSTANCE -value $db2instance
# Look for db2start events for this instance
$Query = @"
<QueryList>
<Query Id="0">
<Select Path="Application">
*[System[Provider[@Name='$db2instance']and (EventID=5)]]
</Select>
</Query>
</QueryList>
"@
$events = Get-WinEvent -FilterXml $Query
ForEach ($Event in $Events) {
# Convert the event to XML
$eventXML = [xml]$Event.ToXml()
Add-Member -InputObject $Event -MemberType NoteProperty -Force -Name Data -Value $eventXML.Event.EventData.Data
$eventXML.Data
}
#$Events | Select TimeCreated, Id, Data | Out-GridView > C:\xtivia\activate_log.txt
#$Events | Select TimeCreated, Id, Data | Where {$_.Data -match "ADM7513W"} | Out-GridView >> $out_file
if ($Events | Select TimeCreated, Id, Data | Where {$_.Data -match "ADM7513W"}) {
#if ($Events.Data -match "ADM7513W") {
Get-Date >> $out_file
Write-Output "db2start detected, activating databases" >> $out_file
$Events | Select TimeCreated, Id, DriverDescription, DriverVersion, ProviderName, @{Name="MessageData";Expression={$_.Message + $_.Data}} | Where {$_.DriverDescription -match "NVIDIA GeForce GTX*"} | Out-GridView
# Activate all DB2 databases.
$db_hash = @{}
db2 list db directory |select-string Indirect -context 10,1 | Select -Expand context | Select -Expand PreContext |select-string 'alias|name' | convertfrom-stringdata | foreach-object {
if ($_."Database alias") { $alias=$_."Database alias" }
if ($_."Database name") { $name=$_."Database name" }
if ( $alias -ne $null -and $name -ne $null ) {
$db_hash.Add($alias, $name)
clear-variable alias
clear-variable name
} #end if on alias and name
} # end foreach-object
$ht = @{}
foreach ($key in $($db_hash.keys)) {
$ht[$db_hash["$key"]] += 1
if ( $ht[$db_hash["$key"]] -gt 1 ) { $db_hash.remove("$key") }
}
foreach ($db in $($db_hash.keys)) {
db2 -v activate db $db >> $out_file
} #end foreach db
} #end if on events.data
Scheduling
While I’m somewhat proud of the script itself, the real power comes in the scheduling using the event scheduler. First, I set up the trigger by specifying the script is triggered by an event and entering the event search string as XML. In this case, I was triggering off of events of ID 5 by this DB2 instance. Now, event ID 5 can be a number of things, so the PowerShell script does further filtering to ensure the event is indeed a db2start. You may need to look in the event viewer to ensure that your system matches these details. Here is what the trigger looks like:
The instance ID must be changed to match what it is on each system. The place to do this is where the pink arrow is pointing.
After that, I specified the action to take when the trigger is detected:
The full action triggered is:
powershell.exe -executionpolicy bypass "& 'C:\xtivia\db2_activate.ps1'"
In this case, the fully qualified name of the PowerShell script is C:\xtivia\db2_activate.ps1
I strongly recommend testing this, and making sure that the script has appropriate permissions set and ensuring you have specified that it be run as the correct ID, which will change depending on your security details.
Summary
This method does mean that events are parsed twice – once with the XML specified in the event triggering and once again by the PowerShell script. But the only events parsed by the PowerShell script are the ones that meet the specified XML in the trigger, which shouldn’t be excessive. You’ll want to prune the log file from time to time, but having this is useful in understanding what is going on.
Hi Ember,
I have a PowerShell script that does the same thing. Additionally I was able to make it parse event log only once, and it is instance-agnostic (i.e. it detects which instance has started and it will only activate databases local to that instance). I’d be happy to share the code – I had been thinking of writing an article on it, but you beat me to it! 😉
I would love to have it if you are willing to share it in an article. I love to present different (and better!) options.