This script uses pure java type 4 drivers to connect to a remote DB2 database, execute a select query and display the results.
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 | ###########################################################
# Connect to DB2 using Type 4 Java driver.
###########################################################
puts "\nexecuting [info script]\n"
# make script drive independent.
set drive [lindex [file split [info nameofexecutable]] 0 ]
puts "\nproclib = $drive/scripts/TCL/proclib"
########################################################
# Source packages.
########################################################
package require java
######################################
# Proc - check mq listener.
######################################
proc db2Connect { serverName databaseName portNumber driverType username password sqlQuery } {
puts "\ndb2Connect \n"
# 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 com.ibm.db2.jcc.DB2Driver
java::import com.ibm.db2.jcc.DB2Driver
# load db2 driver .
java::call Class forName com.ibm.db2.jcc.DB2Driver
# set the connection url.
append url jdbc:db2://
append url $serverName
append url :
append url $portNumber
append url /
append url $databaseName
append url :
append url user\=$username
append url \;
append url password\=$password
append url \;
puts "connection URL is: $url\n"
set ConnectionI [ java::call DriverManager getConnection $url ]
puts "transaction isolation level is [ $ConnectionI getTransactionIsolation ] \n"
puts "Create query\n"
set opt1 [java::field ResultSet TYPE_SCROLL_INSENSITIVE]
set opt2 [java::field ResultSet CONCUR_READ_ONLY ]
set StatementI [ $ConnectionI createStatement $opt1 $opt2 ]
$StatementI execute $sqlQuery
set ResultSetI [ $StatementI getResultSet ]
puts "get a list of return columns\n"
set ResultSetMetaDataI [ $ResultSetI getMetaData ]
set columnCount [ $ResultSetMetaDataI getColumnCount ]
set i 1
while { $i <= $columnCount } {
set columnName [ $ResultSetMetaDataI getColumnName $i ]
lappend columnList $columnName
incr i
}
unset i
puts "loop over the results set and print column name, column value.\n"
while { [ $ResultSetI next ] == 1 } {
foreach i $columnList {
puts [ format "%-5s %-30s %-s" " " "$i" "[ $ResultSetI getString $i ]" ]
}
puts [ format "\n%-5s \n" [ string repeat "#" 50] ]
}
puts "Close Connections\n"
$ResultSetI close
$ConnectionI close
}
######################################
# Main Control.
######################################
# build tcl classpath
append x $drive/IBM/SQLLIB/java/db2jcc.jar;
append x $drive/IBM/SQLLIB/java/db2jcc_license_cu.jar;
append x $drive/IBM/SQLLIB/java/db2jcc_license_cisuz.jar;
set env(TCL_CLASSPATH) $x
puts "\nTCL_CLASSPATH = [ array get env TCL_CLASSPATH ]\n"
set reportFile $drive/reports/notify/mqConnect.txt
set reportFileId [ open $reportFile w ]
set serverName yourserver
set databaseName yourDatabase
set portNumber 50000
set driverType 4
set username yourUserid
set password yourPassword
set sqlQuery "select * from abcd.customer_table where char(customer) like '12345%'"
db2Connect $serverName $databaseName $portNumber $driverType $username $password $sqlQuery
|
Type 4 java drivers allow Tcl Apps to connect to most enterprise databases without the need for Tcl specific ODBC packages.
Tags: tclblend