Hello, Db2 World! PowerShell

I have published several articles on PowerShell. But the “Hello, Db2 World!” series is designed to be a quick and fairly simple way of properly connecting to a Db2 database. Check out the GitHub repository folder for this blog entry.

Db2 Drivers

Note that before starting, you should have a Db2 Client installed on the server you wish to connect from. Follow IBM’s directions for installing IBM Data Server Clients and Drivers. You usually do NOT want the runtime client.

PowerShell and Db2

I use PowerShell most often when I need to administer Db2 databases on MS Windows severs. PowerShell offers several things that make it an excellent command line environment for working with Db2, some commands that are more similar to ksh/Bash, and a sophisticated object orientation as your work gets more advanced.

The Code

I am splitting all code in this series into two files. One that contains private information like passwords and host names, and one that contains the actual working code. For the PowerShell “Hello, Db2 World”, that file looks like this (names and passwords changed to protect the innocent):


Likely all of these variables will have to be changed for you. “localhost” for the server name means that your script is running on the same server where the database is, which is rare unless you are a DBA.

The code looks like this:

# Source File with connection variables set
$Path = $PSScriptRoot
. "$Path\HelloDb2World_PowerShell_variables.ps1"

#Define connection string for the database
$cn = new-object system.data.OleDb.OleDbConnection("Provider=IBMDADB2;DSN=$dbName;User Id=;Password=;");
#Define data set for first query
$ds = new-object "System.Data.DataSet" "ds"
#Define query to run
$q = "select * from hello_world"
# Define data object given the specific query and connection string
$da = new-object "System.Data.OleDb.OleDbDataAdapter" ($q, $cn)
# Fill the data set - essentially run the query. 
$da.Fill($ds) | Out-Null
# Print the result
foreach ($Row in $ds.Tables[0].Rows)
        write-host  "$($Row.C1)"  
# Close the Connection

I don’t claim to be an expert in Powershell. Check out Luke Numrych’s different ideas for using PowerShell with Db2.

Also check out my other PowerShell related blog entries. Most are from the point of view of a DBA, but they may give you some ideas.

All of the code used in the “Hello, Db2 World!” is available on GitHub.

Ember Crooks
Ember Crooks

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, spent a year working with high-volume MySQL, and is now learning Snowflake. Ember shares both posts about her core skill sets and her journey learning Snowflake.

Ember lives in Denver and work from home

Articles: 556


  1. Hello Ember,

    Thanks for your contributions to the DB2 community with the code here and I also spotted that you were also running a session on HADR with Scott.

    I have been trying to make this script run against my Db2 database and it did not display data until I changed the statement write-host “$($Row.C1)” to write-host “$($Row.ItemArray)”

    Is there something that I am missing? I am happy to post my powershell code if it helps.

    Thanks, Peter

  2. I am getting a “The ‘{iSeries Access ODBC Driver}’ provider is not registered on the local machine.” error.

    If I run Get-OdbcDsn it will return the following;
    DsnType : System
    Platform : 64-bit
    DriverName : iSeries Access ODBC Driver
    Attribute : {DelimitNames, System, SQLConnectPromptMode, SortTable…}

    Also, I see variables for the server and port values but they are not used in the other script. How do those values end up getting used?


  3. Hi,
    I am getting the following errr while calling $da.Fill($ds).
    Exception calling “Fill” with “1” argument(s): “ SQL30082N Security processing failed with reason “5” (“USERID MISSING”), SQLSTATE=08001”
    At line:13 char:1
    + $da.Fill($ds) | Out-Null
    + ~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo. : NotSpecified: (:) [],, MethodInvocationException
    + FullyQualifiedErrorId : OleDbException
    Even though I have specified the UserId. I have also tried mentioning it with with the variable and without it. It doesn’t work. Throws the same error.
    I have the DB2 DSN x64 client installed

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.