Welcome, guest | Sign In | My Account | Store | Cart

This script logs the WAS JDBC PMI performance data to a Cloudscape database.

Tcl, 443 lines
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
@echo off

setlocal

TITLE "GETJDBCINFO"

set host=yourserver
set dataBase="D:/DERBYDatabases/yourdatabase"
set schema="yourschema"
set driver="DB2XADataSource"

set server=yourserver

call :GETPMIINFO

goto :EOF

:GETPMIINFO
echo ###################################################
echo # Get PMI info for %server%
echo ###################################################

command.com /c

pushd d:\IBM\WebSphere\AppServer\bin

call d:\IBM\WebSphere\AppServer\bin\wsadmin -conntype SOAP^
                                            -port 8879^
					    -host %host%^
					    -user user^
					    -password password^
                       -f d:\scripts\websphere\jacl\getJDBCINFO.tcl %server% %database% %schema% %driver%


endlocal 

===========================================================================

# 
# Get JDBC PMI info for server. 
# NB: server must be running and PMI must be switched on.
#
####################################################################
# Patrick Finnegan 11/11/2005.  V1. 
####################################################################

####################################################################
# Get Cloudscape connection.
####################################################################
proc dbconnect { databaseName } {

   puts "\n**********"
   puts "dbconnect"
   puts "**********\n"

   global env 
   global null

   # load client driver.
   java::call Class forName org.apache.derby.jdbc.ClientDriver

   append url jdbc:derby
   append url ":" 
   append url "//" 
   append url $::env(com.ibm.ws.scripting.host)
   append url ":" 
   append url "1527" 
   append url "/" 
   append url $databaseName 

   puts "\n connection URL is:  $url\n"   

   java::try {
       
       set ConnectionI [ java::call DriverManager getConnection $url ] 

   } catch {SQLException SQLExceptionI } {

     catchSqlException $SQLExceptionI 
	 
   } catch {TclException e } {
       puts "TCl Exception to prepare statement $e"
       return -code error
   }

   puts "transaction isolation level is [ $ConnectionI getTransactionIsolation ] \n"

   java::lock $ConnectionI 

   return $ConnectionI 

}
####################################################################
# proc - get JDBC details 
####################################################################
proc getJDBCInfo { nodeName serverName ConnectionI schema driver } {

   global AdminConfig 
   global AdminControl
   global null
   global Help

   puts "\n**********"
   puts "getJDBCInfo"
   puts "**********\n"

   puts "\nget the performance mbean for the server - should be just one.\n"

   append queryString "WebSphere:*,node="
   append queryString $nodeName
   append queryString ",process="
   append queryString $serverName
   append queryString ",type=Perf"

   puts "\n query string is: $queryString \n"

   set perfMbean [ $AdminControl queryNames $queryString ]

   if { $perfMbean == $null || [ string length $perfMbean] == 0 } {
       
       puts "\n $serverName is not running or PMI is not enabled for $serverName. \n"

       return 

   } else {

       set donothing true 

   }

   puts "\nget the JDBC Bean.\n"

   unset queryString 

   append queryString "WebSphere:*,node="
   append queryString $nodeName
   append queryString ",name="
   append queryString $driver
   append queryString ",process="
   append queryString $serverName
   append queryString ",type=JDBCProvider"

   puts "\n query string is: $queryString \n"

   set JDBCMbean [ $AdminControl queryNames $queryString ]

   #puts "\n JDBCMbean: $JDBCMbean \n"
   puts "\n length JDBCMbean: [ llength $JDBCMbean ] \n"
   
   if { $JDBCMbean == $null || [ string length $JDBCMbean] == 0 } {
       
       puts "\n $serverName JDBC is not running \n"

       return 

   } else {

       set donothing true 

   }

   set JDBCMbeanO [ $AdminControl makeObjectName $JDBCMbean ]

   set boolean [ java::new Boolean recursive ]
   # NB: brackets around array object. 
   set parramsArray    [ java::new {Object[]} {2} [ list $JDBCMbeanO $boolean ] ]
   set signaturesArray [ java::new {String[]} {2} [ list javax.management.ObjectName java.lang.Boolean ] ]

   set perfMbeanO [ $AdminControl makeObjectName $perfMbean ]

   #get the stats list from the performance bean
   puts "get the stats list from the performance bean"

   set JDBCInfo [ $AdminControl invoke_jmx $perfMbeanO "getStatsString" $parramsArray $signaturesArray   ] 

   if { $JDBCInfo == $null || [ string length $JDBCInfo ] == 0 } {
       
       puts "\n $serverName: No JDBC stats available \n"

       return 

   } else {

       set donothing true 

   }

   set JDBCInfoString [ $JDBCInfo toString ] 

   #get PMI data sub list.
   set e4        [ lindex $JDBCInfoString 3 ]  
   set dataList  [ lindex $e4 1 ]  
   set dataListL [ llength $dataList ]  

   # sort in id order
   set dataList [ lsort -dictionary -index 0 $dataList ] 

   #set topParms [ lindex $dataList 0 ]  
   set topParms [ lindex $JDBCInfoString 1 ]

   set width 60

   set x1  [ lindex [ lindex [ lindex $topParms 1 ] 0 ] 0 ]
   set x2  [ lindex [ lindex [ lindex $topParms 1 ] 0 ] 1 ]
   puts    [ format "%-${width}s %s"  $x1 $x2 ]

   lappend valuesList $x2
      
   set x1  [ lindex [ lindex [ lindex $topParms 1 ] 1 ] 0 ]
   set x2  [ lindex [ lindex [ lindex $topParms 1 ] 1 ] 1 ]
   puts    [ format "%-${width}s %s"  $x1 $x2 ]

   lappend valuesList $x2

   set x1        "Time"
   set x2        [ clock format [ clock seconds ] -format "%Y-%m-%d %H:%M:%S" ]
   puts          [ format "%-${width}s %s"  $x1 $x2 ]

   lappend valuesList $x2

   set x 0 
   set y [ llength $dataList ]

   while { $x < $y } {

      set e                [ lindex $dataList $x ]

      set idIndex          [ lsearch -glob $e {*Id*} ]
      set id               [ lindex $e $idIndex ]

      set pmiDataInfoIndex [ lsearch -glob $e {*PmiDataInfo*} ]
      set pmiDataInfo      [ lindex $e $pmiDataInfoIndex ]

      set nl               [ eval concat [ join $e ] ]
      set commentIndex     [ lsearch -glob $nl {*Comment*} ]
      set comment          [ lindex [ lindex $nl $commentIndex ] 1 ]
      
      set valueIndex       [ lsearch -glob $e {*Value*} ]
      set value            [ lindex $e $valueIndex ] 

      set value [ lrange $value 1 end ]

      set lengthValue [ llength $value ]

      if { $lengthValue == "1" || $lengthValue == "4" } {

         set p [ lindex [ lindex $value 0 ] 1 ]

      } else {

         set p [ lindex [ lindex $value 2 ] 1 ]

      }

      puts [ format "%-${width}s %s"  $comment $p ]

      lappend valuesList $p

      unset p
      incr x 

   }

   #write data to DB2. 
   
   writeData $ConnectionI $valuesList $schema  

   unset valuesList 
}
####################################################################
# proc - write data to database. 
####################################################################
proc writeData { ConnectionI valuesList schema } {

   global AdminConfig 
   global AdminControl
   global null

   puts "\n**********"
   puts "writeData"
   puts "**********\n"

   set opt1 [java::field ResultSet TYPE_SCROLL_INSENSITIVE]
   set opt2 [java::field ResultSet CONCUR_READ_ONLY ]

   #puts "\n values list is $valuesList\n"

   set statement "insert into $schema.wasperformJDBC values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"

   java::try {
       
       set StatementI [ $ConnectionI prepareStatement $statement ]

   } catch {SQLException SQLExceptionI } {

     catchSqlException $SQLExceptionI 
	 
   } catch {TclException e } {
       puts "TCl Exception to prepare statement $e"
       return -code error
   }

   set x 1

   foreach i $valuesList {

         $StatementI setString $x $i  

	 #puts $i  

         incr x

   }

   #puts "execute sqlQuery"

   java::try {
       
       $StatementI executeUpdate   

   } catch {SQLException SQLExceptionI } {

     catchSqlException $SQLExceptionI 
         
   } catch {TclException e } {
       puts "TCl Exception to prepare statement $e"
       return -code error
   }

}
####################################################################
# proc - sqlException. 
####################################################################
proc catchSqlException { SQLExceptionI } {

   global AdminConfig 
   global AdminControl
   global Help
   global null

   puts "\n**********"
   puts "catchSqlException"
   puts "**********\n"

   set sqlCode       [ $SQLExceptionI toString         ]
   set sqlMessage    [ $SQLExceptionI getMessage       ]
   set errorCode     [ $SQLExceptionI getErrorCode     ] 
   set sqlState      [ $SQLExceptionI getSQLState      ] 

   if { $sqlCode    != $null } { lappend msgList "sql code is:       \t$sqlCode" }
   if { $sqlMessage != $null } { lappend msgList "sql message is:    \t$sqlMessage" }
   if { $errorCode  != $null } { lappend msgList "error code is:     \t$errorCode" }
   if { $sqlState   != $null } { lappend msgList "sql state is:      \t$sqlState\n" }

   while { $SQLExceptionI != $null } {

       
     puts "\nget SQL Exception\n" 

     set sqlCode       [ $SQLExceptionI toString         ]
     set sqlMessage    [ $SQLExceptionI getMessage       ]
     set errorCode     [ $SQLExceptionI getErrorCode     ] 
     set sqlState      [ $SQLExceptionI getSQLState      ] 

     set SQLExceptionI [ $SQLExceptionI getNextException ]

     if { $sqlCode       != $null } { lappend msgList "sql code is:       \t$sqlCode"       }
     if { $sqlMessage    != $null } { lappend msgList "sql message is:    \t$sqlMessage "   }
     if { $errorCode     != $null } { lappend msgList "error code is:     \t$errorCode"     }
     if { $sqlState      != $null } { lappend msgList "sql state is:      \t$sqlState"      }

   }

   return -code error $msgList

}
####################################################################
# Main Control.
####################################################################

puts "\n argc = $argc \n"

if {$argc < 4 } {
        return -code error "error - not enough arguments supplied.  Supply server name, database, schema, driver."
}

# import required classes 
java::import java.sql.Connection
java::import java.sql.DriverManager
java::import java.sql.ResultSet
java::import java.sql.SQLWarning
java::import java.sql.Statement
java::import java.sql.ResultSetMetaData 
java::import org.apache.derby.jdbc.ClientDriver

puts "imported classes are:\n"

foreach i [java::import] {
   
  puts [ format "%-5s %-50s" " " $i ] 

}
       
# Assume one cell, one deployment manager node and one application node. 

set cellId [ lindex [ $AdminConfig list Cell ] 0 ]
set nodes  [ $AdminConfig list Node ]

# delete the manager node from the list.

set manIndex   [ lsearch -glob $nodes *Manager* ]
set nodeId     [ lindex [ lreplace $nodes $manIndex $manIndex ] 0 ]

# get name attribute for cell and application node

set cellName [ $AdminConfig showAttribute $cellId name ]
set nodeName [ $AdminConfig showAttribute $nodeId name ]

set serverName   [ lindex $argv 0 ]
set databaseName [ lindex $argv 1 ]
set schema       [ lindex $argv 2 ]
set driver       [ lindex $argv 3 ]

set null [ java::null ]
global null

if { [ catch { dbconnect $databaseName } r ] == 0 } {

   while {1} { 

     after 30000
     getJDBCInfo $nodeName $serverName $r $schema $driver

   }

   $r close

} else {

    puts $r 
    exit 1   

}

The wsadmin bat file must be modified to include the cloudscape jar files on the classpath.

:runcmd

set WAS_CLASSPATH=%WAS_CLASSPATH%;d:\IBM\Cloudscape_10.1\lib\derby.jar set WAS_CLASSPATH=%WAS_CLASSPATH%;d:\IBM\Cloudscape_10.1\lib\derbyclient.jar set WAS_CLASSPATH=%WAS_CLASSPATH%;d:\IBM\Cloudscape_10.1\lib\derbynet.jar set WAS_CLASSPATH=%WAS_CLASSPATH%;d:\IBM\Cloudscape_10.1\lib\derbytools.jar

"%JAVA_HOME%\bin\java" %javaoption% %CONSOLE_ENCODING% %DEBUG% "%CLIENTSOAP%" "%CLIENTSAS%" "-Dcom.ibm.ws.scripting.wsadminprops=%WSADMIN_PROPERTIES%" -Dcom.ibm.ws.management.standalone=true "-Duser.install.root=%USER_INSTALL_ROOT%" "-Dwas.install.root=%WAS_HOME%" "-Dwas.repository.root=%CONFIG_ROOT%" "-Dserver.root=%WAS_HOME%" "-Dlocal.cell=%WAS_CELL%" "-Dlocal.node=%WAS_NODE%" "-Dcom.ibm.itp.location=%WAS_HOME%\bin" "-classpath" "%WAS_CLASSPATH%;%WAS_HOME%\lib\jython.jar" "-Dws.ext.dirs=%WAS_EXT_DIRS%" com.ibm.ws.bootstrap.WSLauncher com.ibm.ws.scripting.WasxShell %*

The JDBC performance table definition is:

-- If the table exists drop the table. -- Otherwise comment out.

--DROP TABLE WASPERFORMJDBC;

CREATE TABLE WASPERFORMJDBC ( "NODE" VARCHAR (20), "SERVER" VARCHAR (20), "TIME" TIMESTAMP, "CREATED" INTEGER, "DISTROYED" INTEGER, "FREED" INTEGER, "ALLOCATED" INTEGER, "AVGPOOLSIZE" INTEGER, "FREECONNECTIONS" INTEGER, "AVGTHREADSWAITCONN" INTEGER, "NUMTIMEOUTS" INTEGER, "AVG%POOLINUSE" INTEGER, "AVG%TIMEALLCONSINUSE" INTEGER, "AVGTIMECONINUSEMSECONDS" FLOAT, "AVGWAITTIMEFORCON" FLOAT, "NUMMGMCONSINUSE" INTEGER, "NUMCONSINUSE" INTEGER, "NUMSTATMENTSDISCARDED" INTEGER, "JDBCTIMEMSECONDS" FLOAT );

--

Created by Patrick Finnegan on Mon, 12 Dec 2005 (MIT)
Tcl recipes (162)
Patrick Finnegan's recipes (56)

Required Modules

  • (none specified)

Other Information and Tasks