Automate DB2 Integration Tasks from PowerShell

Ready to get started?

Download for a free trial:

Download Now

Learn more:

IBM DB2 ADO.NET Provider

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



Are you looking for a quick and easy way to access DB2 data from PowerShell? We show how to use the Cmdlets for DB2 and the CData ADO.NET Provider for DB2 to connect to DB2 data and synchronize, automate, download, 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.

Cmdlets or ADO.NET?

The cmdlets are not only a PowerShell interface to the DB2 API, 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 DB2. To access DB2 data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for DB2.

After obtaining the needed connection properties, accessing DB2 data in PowerShell consists of three basic steps.

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 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 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 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 System.Data.CData.DB2.DB2Parameter("@myId","001d000000YBRseAAH")) $cmd.ExecuteNonQuery()