Back to the main data prep VBA page
I use this script to compile data from multiple DB2 snapshots into a single time series table that can be used to generate time series plots for use in enterprise workload models. Snapshots can be difficult to work with as they are massive text files with lots of detailed diagnostic data that are not particularly well suited to long term workload modeling. This script can help pull key metrics from these text files that can then be used to generate plots for use in stack overlay models.
Sub OSdbSnapshotClean()
'set variables
''''''''''''''
Dim rawLOG As String, rawLOGpath As String, splitArray() As String, splitTime() As String
Dim SNAParr(100000, 4) As Variant, logNameArr(1000, 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 "snapshot_db*.out" Then
logCT = logCT + 1
logNameArr(logCT, 1) = rawLOG
End If
NextLogName:
rawLOG = Dir
Loop
'loop through snapshot logs
'''''''''''''''''''''''''''
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objWrite = objFSO.CreateTextFile(rawLOGpath & "\csvDBsnap.txt", 1)
objWrite.WriteLine "time,Storage path free space (bytes),Total sorts,Total sort time (ms),Buffer pool data logical reads,Buffer pool data physical reads,Buffer pool index logical reads,Buffer pool index physical reads,Total buffer pool read time (milliseconds),Total buffer pool write time (milliseconds),Package cache lookups,Package cache inserts,Package cache high water mark (Bytes),Rows deleted,Rows inserted,Rows updated,Rows selected,Rows read,"
For logLoop = 1 To logCT
srcFILE = rawLOGpath & tgtDir & logNameArr(logLoop, 1) & ""
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objrawLOG = objFSO.OpenTextFile(srcFILE, 1)
Do Until objrawLOG.AtEndOfStream
txnstring = objrawLOG.ReadLine
If txnstring Like "Snapshot timestamp*" Then
splitArray() = Split(txnstring, "=")
timeStamp = splitArray(1)
timeStamp = Replace(timeStamp, " ", "", , 1)
splitTime() = Split(timeStamp, " ")
dateStamp = splitTime(Val(0))
timeStamp = splitTime(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
outString = stampYear & "/" & stampMonth & "/" & stampDay & " " & timeStamp & ","
End If
If txnstring Like "*Storage path free space (bytes)*" Then
splitArray() = Split(txnstring, "=")
outString = outString & Replace(splitArray(Val(1)), " ", "", , 1) & ","
End If
If txnstring Like "Total sorts*" Then
splitArray() = Split(txnstring, "=")
outString = outString & Replace(splitArray(Val(1)), " ", "", , 1) & ","
End If
If txnstring Like "Total sort time (ms)*" Then
splitArray() = Split(txnstring, "=")
outString = outString & Replace(splitArray(Val(1)), " ", "", , 1) & ","
End If
If txnstring Like "Buffer pool data logical reads*" Then
splitArray() = Split(txnstring, "=")
outString = outString & Replace(splitArray(Val(1)), " ", "", , 1) & ","
End If
If txnstring Like "Buffer pool data physical reads*" Then
splitArray() = Split(txnstring, "=")
outString = outString & Replace(splitArray(Val(1)), " ", "", , 1) & ","
End If
If txnstring Like "Buffer pool index logical reads*" Then
splitArray() = Split(txnstring, "=")
outString = outString & Replace(splitArray(Val(1)), " ", "", , 1) & ","
End If
If txnstring Like "Buffer pool index physical reads*" Then
splitArray() = Split(txnstring, "=")
outString = outString & Replace(splitArray(Val(1)), " ", "", , 1) & ","
End If
If txnstring Like "Total buffer pool read time (milliseconds)*" Then
splitArray() = Split(txnstring, "=")
outString = outString & Replace(splitArray(Val(1)), " ", "", , 1) & ","
End If
If txnstring Like "Total buffer pool write time (milliseconds)*" Then
splitArray() = Split(txnstring, "=")
outString = outString & Replace(splitArray(Val(1)), " ", "", , 1) & ","
End If
If txnstring Like "Package cache lookups*" Then
splitArray() = Split(txnstring, "=")
outString = outString & Replace(splitArray(Val(1)), " ", "", , 1) & ","
End If
If txnstring Like "Package cache inserts*" Then
splitArray() = Split(txnstring, "=")
outString = outString & Replace(splitArray(Val(1)), " ", "", , 1) & ","
End If
If txnstring Like "Package cache high water mark (Bytes)*" Then
splitArray() = Split(txnstring, "=")
outString = outString & Replace(splitArray(Val(1)), " ", "", , 1) & ","
End If
If txnstring Like "Rows deleted*" Then
splitArray() = Split(txnstring, "=")
outString = outString & Replace(splitArray(Val(1)), " ", "", , 1) & ","
End If
If txnstring Like "Rows inserted*" Then
splitArray() = Split(txnstring, "=")
outString = outString & Replace(splitArray(Val(1)), " ", "", , 1) & ","
End If
If txnstring Like "Rows updated*" Then
splitArray() = Split(txnstring, "=")
outString = outString & Replace(splitArray(Val(1)), " ", "", , 1) & ","
End If
If txnstring Like "Rows selected*" Then
splitArray() = Split(txnstring, "=")
outString = outString & Replace(splitArray(Val(1)), " ", "", , 1) & ","
End If
If txnstring Like "Rows read*" Then
splitArray() = Split(txnstring, "=")
outString = outString & Replace(splitArray(Val(1)), " ", "", , 1) & ","
End If
Loop
objWrite.WriteLine outString
outString = ""
Next logLoop
End Sub