'-----------------------------------------------------------------------------
'- Snapshot.vbs - Display DB2 Snapshot Information
'-----------------------------------------------------------------------------
'- Arguments .....: DBName - Database Name
'-                  DBUser - Authorized User ID
'-                  DBPsw  - Users Password
'- Calls .........: NOTEPAD.EXE to display data
'-----------------------------------------------------------------------------
'- Requires ......: - Windows Scripting Shell 5.6 or higher
'-                  - IBM DB2 ADO Support 2.8 or higher
'-                  - IBM DB2 Run Time Client V8.2 or higher
'-                  - IBM DB2 Database Server V8.2 FixPak 9 or higher
'- Changes .......: First release 29.07.2005/GR
'- 02.08.2005/GR  : V1.1 Excessive data volume, method to present data
'-                       changed from sendkeys/notepad to fso/notepad
'-----------------------------------------------------------------------------

Option Explicit  
'On Error Resume Next

'--- Constants ---------------------------------------------------------------
Const conScriptVersion = "1.1"
Const constForReading = 1
Const constForWriting = 2

'--- Variables ---------------------------------------------------------------
Dim objShell'Object Shell
Dim objExec'Object Exec external program
Dim objRun'Object Run external program
Dim objArgs'Object Arguments
Dim objEnv'Object Environment
Dim strWSHVersion'WSH Version
Dim strScriptName'Script Name
Dim strScriptFullName'Full Script Name
Dim strCurrDir'Current Directory

Dim strDBName'Database
Dim strDBUser'User
Dim strDBPsw'Password

Dim objCon 'connection Object
Dim objFSO'File System Object
Dim objF'File Object
Dim strSQLStmt'SQL Statement
Dim arrData'Output Data
Dim datTimestamp'current date/time
Dim strTempPath'Name of system /temp path
Dim strOutputFile'Full Name of output file

Dim intWork
Dim strWork

'--- Retrieve Script Information ---------------------------------------------
Set objShell = WScript.CreateObject("Wscript.Shell")
Set objEnv= objShell.Environment("USER")'Environment type
strTempPath= objEnv("TEMP")'Environment variable
strWSHVersion = Wscript.Version
strScriptName = Wscript.ScriptName
strScriptFullName =WScript.ScriptFullname
strCurrDir= objShell.CurrentDirectory
strOutputFile= strTempPath & "\" & left(strScriptName,len(strScriptName)-4) & "-" & FormatDateTime(Date,0)

'Wscript.Echo strWSHVersion, strScriptName, strScriptFullName, strCurrDir
'Wscript.Echo strOutputFile

if strWSHVersion <> "5.6" then
MsgBox "This script requires WSH Version 5. or higher!" , _
16, _
"Display DB2 Snapshot Information - " & strScriptFullName
Wscript.Quit
end if

'--- Set Link on Desktop  ----------------------------------------------------
SetLink

'--- Check Arguments ---------------------------------------------------------
Set objArgs = WScript.Arguments
If objArgs.Count < 2 then
intWork = MsgBox("No or invalid arguments passed!" & Chr(10) & Chr(10) & _
"Usage: (on command prompt) <" & strScriptName & " dbname userid password>" & _
chr(10) & chr(10) & _
"Do you want to specify parameter right now?", _
33, _
"Display DB2 Snapshot Information - " & strScriptFullName)
if intWork = 1 then 'OK Button pressed
strDBName = InputBox("Enter Database name (e.g. DB5STAR):")
strDBName = UCase(strDBName)
strDBUser = InputBox("Enter User name for Database " & strDBName & " (e.g. dwhadmin):")
strDBPsw = InputBox("Enter User's " & strDBUser & " Password:")
if strDBName = "" or strDBUser = "" or strDBPsw = "" then
MsgBox "No or invalid arguments passed - script abends now!" & _
Chr(10) & Chr(10) & _
"Usage: <" & strScriptName & " dbname userid password>" , _
16, _
"Display DB2 Snapshot Information - " & strScriptFullName
Wscript.Quit
end if
else'CANCEL button pressed
Wscript.Quit
end if
else
'Assign positional arguments
strDBName=objArgs(0)
strDBUser=objArgs(1)
strDBPsw=objArgs(2)
end if

strDBName = UCase(strDBName)

'--- Connect to Database -----------------------------------------------------
objShell.Popup "This may take some seconds ..." & _
chr(13) & chr(13) & _
"[This pop-up automatically closes!]" , 3, "Please Wait!", 0 + 64
DB2Connect()'Connect to database

'--- Open Output File to write collected information on ----------------------
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objF = objFSO.OpenTextFile(strOutputFile, constForWriting, True)
datTimestamp = now
objF.WriteLine "File "& strOutputFile & " opened at " & datTimestamp
objF.WriteBlankLines(1)

'--- Provide DB2 Server Information ------------------------------------------
DB2InfoBlock(1)

'--- Write collected Information to NOTEPAD ----------------------------------
objF.WriteBlankLines(1)
objF.WriteLine "Database Snapshot for " & strDBName & ":"
objF.WriteLine "==================================="
arrData= DB2Select("SELECT * FROM TABLE(snapshot_database('',-1)) as T")

objF.WriteBlankLines(1)
objF.WriteLine "Table Snapshot for " & strDBName & ":"
objF.WriteLine "==================================="
objF.WriteBlankLines(1)
arrData= DB2Select("SELECT * FROM TABLE(snapshot_table('',-1)) as T")

objF.WriteBlankLines(1)
objF.WriteLine "Application Snapshot for " & strDBName & ":"
objF.WriteLine "======================================="
objF.WriteBlankLines(1)
arrData= DB2Select("SELECT * FROM TABLE(snapshot_appl_info('',-1)) as T")

objF.WriteBlankLines(1)
objF.WriteLine "Lock Snapshot for " & strDBName & ":"
objF.WriteLine "=================================== "
objF.WriteBlankLines(1)
arrData= DB2Select("SELECT * FROM TABLE(snapshot_lock('',-1)) as T")
objF.WriteBlankLines(1)
arrData= DB2Select("SELECT * FROM TABLE(snapshot_lockwait('',-1)) as T")

objF.WriteBlankLines(1)
objF.WriteLine "Statement/Dynamic SQL Snapshot for " & strDBName & ":"
objF.WriteLine "================================================"
objF.WriteBlankLines(1)
arrData= DB2Select("SELECT * FROM TABLE(snapshot_statement('',-1)) as T")
objF.WriteBlankLines(1)
arrData= DB2Select("SELECT * FROM TABLE(snapshot_dyn_sql('',-1)) as T")

'--- Disconnect from Database ------------------------------------------------
datTimestamp = now
objF.WriteLine "Generated at "& datTimestamp & " by " & strScriptName & _
" Version " & conScriptVersion
objF.WriteLine "*** end of file ***"
objF.Close

'--- Disconnect from Database ------------------------------------------------
DB2Disconnect

'--- Open NOTEPAD to view collected information on ---------------------------
'set objExec = objShell.Exec("notepad.exe")
objShell.Run "%windir%\notepad " & strOutputFile,1
'WScript.Sleep 100
'objShell.AppActivate "Editor"
'WScript.Sleep 100

Wscript.Quit

'#############################################################################
'# DB2Select: Access DB2 object using SQL SELECT statement
'#############################################################################
Function DB2Select(strSel)
   Dim rsData'Recordset with data
   Dim lngI
   Dim lngZ
   Dim strTemp

   set rsData=WScript.CreateObject("ADODB.Recordset")
   rsData.Open strSel, objCon  
If rsData.recordcount>750 Then
objShell.Popup "Large result set (" & rsData.recordcount & " rows), please wait!" & _
chr(13) & chr(13) & _
"[This pop-up automatically closes!]" , _
3, "Please take notice!", 0 + 64
end if

'--- got result? -----------------------------------------------------
'If rsData.recordcount>500 Then
'objShell.Popup "Result Set > 500 rows! Truncated!", 3, "Please take notice!", 0 + 64
'end if
If rsData.recordcount=0 Then
objShell.Popup "Sorry, not result for query '" & _
strSel & "' available!" & _
chr(13) & chr(13) & _
"[This pop-up automatically closes!]", _
3, "Please take notice!", 0 + 64
end if
If rsData.recordcount>0 Then
rsData.MoveFirst
lngZ = 1
Do While rsData.eof=false
objF.WriteLine "Subject #" & lngZ & ":"
'--- write row with all columns ----------------------
   for lngI=0 to rsData.fields.count-1
objF.WriteLine left(rsData.fields(lngI).name&String(26, "."),26) & ": " & rsData.fields(lngI).value
next
'--- get next row ------------------------------------
lngZ=lngZ+1
if lngZ = 2500 then
objShell.Popup "... stil running, please wait!" & _
chr(13) & chr(13) & _
"[This pop-up automatically closes!]" , _
3, "Please take notice!", 0 + 64
end if
rsData.MoveNext
objF.WriteBlankLines(1)
loop
end if
'--- Close Recordset -------------------------------------------------
   rsData.Close
set rsData=nothing
End Function

'#############################################################################
'# DB2SelectArr: Access DB2 object using SQL SELECT statement, Data in Array
'#############################################################################
Function DB2SelectArr(strSel)
   Dim arrD()
   Dim rsData'Recordset with data
   Dim strTemp
   Dim objFeld
   Dim lngI
   Dim lngZ
   set rsData=WScript.CreateObject("ADODB.Recordset")
   rsData.Open strSel, objCon  

   Redim arrD (rsData.RecordCount, rsData.Fields.Count-1)
'get column names
If rsData.recordcount>0 Then
rsData.MoveFirst
   for lngI=0 to rsData.fields.count-1
arrD(0,lngI)= rsData.fields(lngI).name
 next
End If
'get column data
lngZ=1
Do While rsData.eof=false
   for lngI=0 to rsData.fields.count-1
arrD(lngZ,lngI)= rsData.fields(lngI).value
 next  
lngZ=lngZ+1
rsData.MoveNext
Loop
   rsData.Close
set rsData=nothing
DB2SelectArr=arrD
End Function

'#############################################################################
'# DB2Select: Zugriff auf Objekt
'#############################################################################
Sub DB2Connect()
   Dim strCon
   Dim strVersion
On Error Resume Next
      strCon="Provider=IBMDADB2;DSN=" & strDBName & _
      ";User Id=" & strDBUser & _
";Password=" & strDBPsw
      'Wscript.Echo strCon
      set objCon=WScript.CreateObject("ADODB.Connection")
      strVersion=objCon.Version
'Wscript.Echo "ADO Version " & strVersion
objCon.ConnectionString=strCon
      objCon.CursorLocation=3
      objCon.Open
      If Err.Number <>0 Then
          MsgBox "Connection to Database " & strDBName & _
   " using User " & strDBUser & " not possible. (Invalid database or user name, or invalid password.)" , vbInformation
   Wscript.Quit
      End If
      set objFSO=nothing
End Sub

'#############################################################################
'# DB2Select: Disconnect from DB2 Database
'#############################################################################
Sub DB2Disconnect()
      On Error Resume Next
      objCon.Close
      set objCon=nothing
End Sub

'#############################################################################
'# SetLink: Sets link to this script on Desktop
'#############################################################################
Sub SetLink()
   Dim strDesktop
   Dim oShellLink
  
strDesktop = objShell.SpecialFolders("Desktop")
        set oShellLink = objShell.CreateShortcut(strDesktop & "\Display DB2 Snapshots.lnk")
        oShellLink.TargetPath = left(strScriptFullName,len(strScriptFullName)-3) & "wsh"
        oShellLink.WindowStyle = 1
        oShellLink.Hotkey = "CTRL+SHIFT+S"
        oShellLink.IconLocation = "wscript.exe, 0"
        oShellLink.Description = "Snapshot.vbs - Display DB2 Snapshots"
        oShellLink.WorkingDirectory = strDesktop
        oShellLink.Save

End Sub

'#############################################################################
'# DB2InfoBlock: Provide Information about DB2 Server
'#############################################################################
Function DB2InfoBlock(intType)'0=echo 1=send
  
   arrData= DB2SelectArr("SELECT * FROM TABLE(ENV_GET_SYS_INFO()) as T")
   If intType = 1 then
objF.WriteLine String(70, "-")
objF.WriteLine "-- Server..: " & left(arrData(1,3),8) & " " & _
arrData(1,0) & " Ver. " & arrData(1,2) & _
", " & trim(arrData(1,4)) & " CPU's, " & _
trim(arrData(1,6)) & " MB Memory"
end if

   arrData= DB2SelectArr("SELECT * FROM TABLE(ENV_GET_INST_INFO()) as T")
   If intType = 1 then
objF.WriteLine "-- Instance: " & left(arrData(1,0),8) & " " & _
arrData(1,5) & " FixPak " & arrData(1,8)
objF.WriteLine String(70, "-")
end if

End Function