Back to the main data prep VBA page
Here’s a relatively obscure Excel VBA script that you would likely never use, but it is helpful if you care about IBM DB2 buffer cache sizes over time that can be correlated to other supporting infrastructure stack events. Not too many folks use this Excel VBA script, but I find it helpful debugging problematic DB2 instances.
[vb]
Sub OSdb2diagClean()
‘set variables
”””””””
Dim rawLOG As String, rawLOGpath As String, splitArray() As String
Dim DIAGarr(100000, 4) As Variant, logNameArr(100, 1) As Variant
Dim rowCT As Double
rowCT = 0
pathFull = Sheets("input").Range("C13")
Set objrawLOG = CreateObject("Scripting.FileSystemObject")
rawLOGpath = objrawLOG.GetFile(pathFull).ParentFolder.path
tgtDir = "\DBtemp\"
‘build array of log names up front, use those as source
”””””””””””””””””””””””””””’
rawLOG = Dir(rawLOGpath & tgtDir)
logCT = 0: logSO = 0
Do While rawLOG <> ""
If rawLOG Like "db2diag*.log" Then
logCT = logCT + 1
logNameArr(logCT, 1) = rawLOG
End If
NextLogName:
rawLOG = Dir
Loop
‘loop through db2diag logs
”””””””””””””
For logLoop = 1 To logCT
srcFILE = rawLOGpath & tgtDir & logNameArr(logLoop, 1) & ""
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objrawLOG = objFSO.OpenTextFile(srcFILE, 1) ‘rawLOGpath & "\" & srcFILE
procNL = 0
Do Until objrawLOG.AtEndOfStream
txnstring = objrawLOG.ReadLine
If txnstring Like "*LEVEL: Info" Then
splitArray() = Split(txnstring, " ")
timeStamp = splitArray(0)
timeStamp = Left(timeStamp, Len(timeStamp) – 4)
timeStamp = Replace(timeStamp, "-", " ", , 3)
timeStamp = Replace(timeStamp, " ", "-", , 2)
timeStamp = Replace(timeStamp, ".", ":", , 2)
splitArray() = Split(timeStamp, " ")
dateStamp = splitArray(Val(0))
timeStamp = splitArray(Val(1))
stampYear = Year(dateStamp)
stampMonth = Month(dateStamp)
If stampMonth < 10 Then stampMonth = "0" & stampMonth
stampDay = Day(dateStamp)
If stampDay < 10 Then stampDay = "0" & stampDay
timeStamp = stampYear & "/" & stampMonth & "/" & stampDay & " " & splitArray(Val(1)) & ","
End If
If txnstring Like "MESSAGE : Altering bufferpool*" Then
rowCT = rowCT + 1
DIAGarr(rowCT, 1) = timeStamp
splitArray() = Split(txnstring, """")
DIAGarr(rowCT, 2) = splitArray(Val(1))
DIAGarr(rowCT, 3) = splitArray(Val(3))
If Len(txnstring) – Len(Replace(txnstring, """", "")) = 6 Then DIAGarr(rowCT, 4) = splitArray(Val(5)) Else procNL = 1
GoTo nextLine:
End If
If procNL = 1 Then
splitArray() = Split(txnstring, """")
DIAGarr(rowCT, 4) = splitArray(Val(1))
procNL = 0
End If
nextLine:
Loop
Next logLoop
‘write to file
”””””””
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objWrite = objFSO.CreateTextFile(rawLOGpath & "\csvDBdiag.txt", 1)
For i = 1 To rowCT
For j = 1 To 4
outString = outString & DIAGarr(i, j) & ","
Next j
objWrite.WriteLine outString
outString = ""
Next i
End Sub
[/vb]