Accessing DB2 Mainframe (z/OS) from PowerShell

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()