Learn more
 

Ready to get started?

Download a free trial of the IBM DB2 Data Provider to get started:

 Download Now

Learn more:

IBM DB2 Icon IBM DB2 ADO.NET Provider

Rapidly create and deploy powerful .NET applications that integrate with IBM DB2.

In this article

Related articles

Automate DB2 Integration Tasks from PowerShell


Are you in search of a quick and easy way to access DB2 data from PowerShell? This article demonstrates how to utilize the DB2 Cmdlets for tasks like connecting to DB2 data, automating operations, downloading data, and more.

The CData Cmdlets for DB2 are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to DB2.

PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to DB2, but also an SQL interface; this tutorial shows how to use both to create, retrieve, update, and delete DB2 data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for IBM DB2. To access DB2 data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for IBM DB2.

Once you have acquired the necessary connection properties, accessing DB2 data in PowerShell can be enabled in three steps.

Set the following properties to connect to DB2:

  • Server: Set this to the name of the server running DB2.
  • Port: Set this to the port the DB2 server is listening on.
  • Database: Set this to the name of the DB2 database.
  • User: Set this to the username of a user allowed to access the database.
  • Password: Set this to the password of a user allowed to access the database.

You will also need to install the corresponding DB2 driver:

  • Windows: Install the IBM Data Server Provider for .NET.

    On Windows, installing the IBM Data Server Provider is sufficient, as the installation registers it in the machine.config.

  • Java: Install the IBM Data Server Driver for JDBC.

    In the Java version, place the IBM Data Server Driver JAR in the www\WEB-INF\lib\ folder for this application.

PowerShell

  1. Install the module:

    Install-Module DB2Cmdlets

  2. Connect:

    $db2 = Connect-DB2 -Server "$Server" -Port "$Port" -User "$User" -Password "$Password" -Database "$Database"

  3. Search for and retrieve data:

    $shipcity = "New York" $orders = Select-DB2 -Connection $db2 -Table "Orders" -Where "ShipCity = `'$ShipCity`'" $orders

    You can also use the Invoke-DB2 cmdlet to execute SQL commands:

    $orders = Invoke-DB2 -Connection $db2 -Query 'SELECT * FROM Orders WHERE ShipCity = @ShipCity' -Params @{'@ShipCity'='New York'}

ADO.NET

  1. Load the provider's assembly:

    [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for IBM DB2\lib\System.Data.CData.DB2.dll")

  2. Connect to DB2:

    $conn= New-Object System.Data.CData.DB2.DB2Connection("Server=10.0.1.2;Port=50000;User=admin;Password=admin;Database=test;") $conn.Open()

  3. Instantiate the DB2DataAdapter, execute an SQL query, and output the results:

    $sql="SELECT OrderName, Freight from Orders" $da= New-Object System.Data.CData.DB2.DB2DataAdapter($sql, $conn) $dt= New-Object System.Data.DataTable $da.Fill($dt) $dt.Rows | foreach { Write-Host $_.ordername $_.freight }

Update DB2 Data

PowerShell

Update-DB2 -Connection $DB2 -Columns @('OrderName','Freight') -Values @('MyOrderName', 'MyFreight') -Table Orders -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.DB2.DB2Command("UPDATE Orders SET ShipCity='New York' WHERE Id = @myId", $conn) $cmd.Parameters.Add((New-Object System.Data.CData.DB2.DB2Parameter("@myId","10456255-0015501366"))) $cmd.ExecuteNonQuery()

Insert DB2 Data

PowerShell

Add-DB2 -Connection $DB2 -Table Orders -Columns @("OrderName", "Freight") -Values @("MyOrderName", "MyFreight")

ADO.NET

$cmd = New-Object System.Data.CData.DB2.DB2Command("INSERT INTO Orders (ShipCity) VALUES (@myShipCity)", $conn) $cmd.Parameters.Add((New-Object System.Data.CData.DB2.DB2Parameter("@myShipCity","New York"))) $cmd.ExecuteNonQuery()

Delete DB2 Data

PowerShell

Remove-DB2 -Connection $DB2 -Table "Orders" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.DB2.DB2Command("DELETE FROM Orders WHERE Id=@myId", $conn) $cmd.Parameters.Add((New-Object System.Data.CData.DB2.DB2Parameter("@myId","001d000000YBRseAAH"))) $cmd.ExecuteNonQuery()