###########################################################
# 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
