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.

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

 

Back to the main data prep VBA page