To access DB2 on Mainframe (z/OS), a DB2 ODBC driver must be downloaded from IBM and installed.
A driver may be downloaded from the IBM web site:
http://www-933.ibm.com/support/fixcentrl/swg/downloadFixes
For example, "
v9.,7fp11_ntx64_odbc_cli.zip" may be used.
After downloading it, unzip it in
C:\Program Files\IBM.
Once unzipped, execute the following in the command line as the system administrator:
C:>
cd "\Program Files\IBM\clidriver\bin"
C:>
db2cli install -setup
Use the Administrative tool "Data Sources (ODBC)" to ensure that the driver is installed properly. If not, the command can be executed again. Since
db2cli is not very verbose, it may be necessary to do it again. Also, note that if you run it as a regular user, it will display the success message but the installation does not occur.
Once you have it, you can then connect to DB2 using
System.Data.Odbc.OdbcConnection as shown here:
$db2conn = New-Object System.Data.Odbc.OdbcConnection
$cstr = "Driver={IBM DB2 ODBC DRIVER - C_PROGRA~1_IBM_CLIDRI~1};"
$cstr += "Database=DB2Database;Hostname=DB2Server;Port=5000;"
$cstr += "UID=DB2User;PWD=DB2Pswd;"
$db2conn.ConnectionString = $cstr
$db2conn.Open()
From here, you can use all the ODBC functions to interact with DB2.
A prepared statement can be used with ODBC. To do so, use
OdbcCommand() to submit a query with bind variables specified with
? for each. For strings, there is no need to surround it with quotes. Then specify the value for them with
Parameters.Add(). The first
Parameters.Add() corresponds to the first
?, the second to the second
?, and so on. The variable types can be specified with
[System.Data.Odbc.OdbcType] enumerator. The supported column types are: BigInt, Binary, Bit, Char, Date, DateTime, Decimal, Double, Image, Int, NChar, NText, Numeric, NVarChar, Real, SmallDateTime, SmallInt, Text, Time, Timestamp, TinyInt, UniqueIdentifier, VarBinary, and VarChar.
$qry = @'
SELECT col1, col2, ..., coln
FROM table
WHERE col1 LIKE ?
'@
$stmt = New-Object System.Data.Odbc.OdbcCommand($qry, $db2conn)
$stmt.Parameters.Add('@col1', [System.Data.Odbc.OdbcType]::varchar, 20).Value = "$col1%"
try {
$xr = $stmt.ExecuteReader()
while ($xr.Read()) {
# do something with the returned data, which is the form of
# $xr.GetValue(column-number-starting-at-0)
}
}
catch {
Write-Host "Executing DB2 $qry failed"
Write-Host $Error[0].Exception
Exit(-2)
}
$stmt.Dispose()
