PowerShell for DB2 DBAs – Part 1

1. Pingback: DB2 Hub | PowerShell for DB2 DBAs – Part 1

2. I am having difficulty get DB2 Data Server Provider for .NET installed.

   When using the GetFactory(IBM.Data.DB2)…. I get
   Exception calling “GetFactory with “1” argument(S): “Unable to find the
   requested .Net Framework Data Provider. It may not be installed.”
   What am I missing on getting the piece installed?

   $factory = [System.Data.Common.DbProviderFactories]::GetFactory(IBM.
   Data.DB2)

   Reply

   1. Glenn,
      Assuming that you have installed an IBM .NET client on your system by installing one of: IBM DB2 Database Server, Client, Runtime Client, or the Data Server Driver (dsdriver) packages, this is most likely due to a failed DB2 .NET client registration. Please verify the output of the following command:
      [System.Data.Common.DbProviderFactories]::GetFactoryClasses() | Format-Table -Property InvariantName, Description -Autosize
      In the output you should see, among others, the following entries:

      IBM.Data.DB2 – the one you need
      IBM.Data.DB2.#.#.# (where #.#.# is dependent on the version of the DB2 .NET client you have installed in your system)

      If you do not have a client installed, you can get one from http://www-01.ibm.com/support/docview.wss?uid=swg21385217 (Download initial Version 10.5 clients and drivers).
      If you have installed the client, yet you do not see the factory in the output, you will need to re-register it, or if all else fails, re-install it. You can consult the following support pages for help on re-registering the client:
      http://www-01.ibm.com/support/docview.wss?uid=swg21429586 (How to register .NET provider after installation of DB2)
      http://www-01.ibm.com/support/docview.wss?uid=swg21618434 (Setup Recommendations for Running Applications on Windows 8 or Windows Server 2012″)

      Reply

3. Thank you for your quick response. This DB2 application is Tivoli Storage Manager. It appears that the DB2 installation that is provided by TSM does not include the client. I am not sure if it is possible or allowed to install the DB2 client.

   PS C:\offlinereorg> [System.Data.Common.DbProviderFactories]::GetFactoryClasses() |
   Format-Table -Property InvariantName, Description -Autosize

   InvariantName Description
   ————- ———–
   System.Data.Odbc .Net Framework Data Provider for Odbc
   System.Data.OleDb .Net Framework Data Provider for OleDb
   System.Data.OracleClient .Net Framework Data Provider for Oracle
   System.Data.SqlClient .Net Framework Data Provider for SqlServer

   PS C:\offlinereorg> db2level
   DB21085I This instance or install (instance name, where applicable: “SERVER1”)
   uses “64” bits and DB2 code release “SQL10055” with level identifier
   “0606010E”.
   Informational tokens are “DB2 v10.5.500.111”, “special_33893”, “IP23628_33893”,
   and Fix Pack “5”.
   Product is installed at “C:\PROGRA~1\Tivoli\TSM\db2” with DB2 Copy Name

   Reply

   1. Unfortunately, I have no experience with TSM, and I do not know how does it install DB2. It would be best if you could consult an expert.
      However, the DB2 Data Server Driver package is a no-fee license as far as I know, so it should follow that if your DB2 Server installation that came with TSM includes .NET drivers (there is a pretty good chance it does) you could just register them. To do so, verify whether the bin directory under “C:\PROGRA~1\Tivoli\TSM\db2″ contains the program db2lswtch, and try registering the client as described in http://www-01.ibm.com/support/docview.wss?uid=swg21429586 (How to register .NET provider after installation of DB2).
      Failing that, you could install the Data Server Driver package separately on the system, but consult TSA support first to verify the validity of this solution.
      Failing that, and provided that your “SERVER1” instance is enabled for remote access, you could install the Data Server Driver package on another system, and access the instance and the database remotely from that system via PowerShell/.NET DB2 Data Provider.

      Reply

4. Mystery of life at last I’ve found you!

   Great explanation about PowerShell the various Microsoft-centric products.

   I’m an IBM DBA transitioning to the Microsoft world.
   Thanks!

   Reply

5. Getting this exception when i try to connect to DB2 using powershell

   Exception setting “ConnectionString”: “ERROR [HY000] [IBM][CLI Driver]
   ERROR [HY000] [IBM][CLI Driver]
   ERROR [HY000] [IBM][CLI Driver]
   ERROR [HY000] [IBM][CLI Driver]
   ERROR [HY000] [IBM][CLI Driver]
   ERROR [HY000] [IBM][CLI Driver]
   ERROR [HY000] [IBM][CLI Driver]
   ERROR [] [IBM][CLI Driver] SQL0000W Statement processing was successful.”
   At line:8 char:1
   + $dbconn.ConnectionString = $cstrbld.ConnectionString
   + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   + CategoryInfo : NotSpecified: (:) [], SetValueInvocationException

   Reply

   1. Hi Vikram,
      The error message you posted is too generic for me to diagnose the problem, but it seems that your connection string is not correct. Can you post the piece of code that builds it (make sure to redact credentials and other sensitive info). It may also be helpful to see what does the connection string look like after it is built – what is in $cstrbld.ConnectionString BEFORE you assign that value to $dbconn.ConnectionString.

      Reply

6. Fantastico!
   It works fine for me except the fact that the result I get, is the record count 43. How do I see the contents of the table? I guess I’ll be up late tonight..
   Oh and one more question: wondering if I can issue the Export command with this.
   dbcmd.CommandText =”EXPORT to C:\Lukeisawesome\employee of DEL Select * from db2admin.employee”
   ——————-
   $factory = [System.Data.Common.DbProviderFactories]::GetFactory(“IBM.Data.DB2”)
   $cstrbld = $factory.CreateConnectionStringBuilder()
   $cstrbld.Database = “SAMPLE”
   $cstrbld.UserID = “db2admin”
   $cstrbld.Password = “Ducati1098”
   $cstrbld.Server = “localhost:50001”
   $dbconn = $factory.CreateConnection()
   $dbconn.ConnectionString = $cstrbld.ConnectionString
   $dbconn.Open()
   $dbcmd = $factory.CreateCommand()
   $dbcmd.Connection = $dbconn

   $dbcmd.CommandText = “select * from Allanxxx.empdemo”
   $dbcmd.CommandType = [System.Data.CommandType]::Text
   $da = $factory.CreateDataAdapter()
   $da.SelectCommand = $dbcmd
   $ds = New-Object System.Data.DataSet
   $da.Fill($ds)
   $dbconn.Close()

   Reply

   1. Hi Allan,

      The data sets returned are accessible through the Tables property of the DataSet object. In your example above, that would be $ds.Tables. See example of how to access that data here: https://docs.microsoft.com/en-us/dotnet/api/system.data.dataset.tables?view=netframework-4.8.
      As to your second question – I do not think this will work as you have it because “EXPORT” is a DB2 Command Line Processor command rather than a SQL command. However, “EXPORT” can be executed through a call to the ADMIN_CMD stored procedure (see https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0023573.html). That should certainly work, but you will most likely need to change how you invoke the command (since it is not a query anymore). Instead of executing it through the $da.Fill($ds) call, I would try executing it through the ExecuteNonQuery method of the $dbcmd object (see https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.swg.im.dbclient.adonet.ref.doc/doc/DB2CommandClassExecuteNonQueryMethod.html).
      Additionally, remember that the EXPORT command will expect file paths relative to the DB2 server – if the export file path is not valid or not accessible from the perspective of the server, it will not work.

      Reply

      1. Hi Luke, happy Sunday…Good stuff!
         I ended up using a method where you don’t have to install anything else but just the IBM.Data.DB2.dll file. Thanks to you, I’m supa close to my objective (finding and replacing SSN’s using REGEX in XML and CLOB fields.

         Here’s the code that’s working for me from my Windows 10 machine to a DB2 instance on another machine in my home network (for now, I’m testing against the SAMPLE Employee table:)

         ——————–
         [System.Reflection.Assembly]::LoadWithPartialName(“System”) | out-null
         [System.Reflection.Assembly]::LoadWithPartialName(“System.object”) | out-null
         [System.Reflection.Assembly]::LoadWithPartialName(“System.Data”) | out-null
         [System.Reflection.Assembly]::LoadWithPartialName(“System.Data.Common.DbDataReader”) | out-null
         [System.Reflection.Assembly]::loadfile( “C:\users\Allan Martin\desktop\IBM.Data.DB2.dll”)
         $Conn = new-object IBM.Data.DB2.DB2Connection(“Database=SAMPLE;User ID=Allanxxx;Password=Ducati1098;server=Blade:50001”)
         $Conn.open()
         $cmd = $conn.CreateCommand()
         $cmd.CommandText = “SELECT * from Allanxxx.employee”
         $myreader = $cmd.ExecuteReader()
         $cols= $myreader.FieldCount;
         $rows=$myreader.HasRows;
         while ($myreader.Read() -eq $rows)
         {
         $line = $myreader.GetString(1)
         write-host $line
         }
         $myreader.Close();
         $conn.Close();
         ———————————-
         Next step is to modify and update the field. I think challenge will be to update the row. This will probably work against small tables but what if the table has millions of rows (which is pretty standard )…

         Thanks,
         Allan

         Reply

7. Hi, Luke!

   Thank you for sharing this. It really saved me a lot of time. By the comments here we can see how many people your article has helped over the years. That’s very nice.

   Nowadays Powershell has a great support for classes, so in my use case I wrote a simple wrapper class to serve as a “DB2 adapter”, following entirely the sample code you’ve provided. This class allows us to simply call a “Connect” method (passing server, database, user and password) and then a “Run” method (passing the query). The “Run” method returns the actual list of rows resulting from the query.
   _________

   class DB2Adapter {
   [IBM.Data.DB2.DB2Factory]$Factory
   [IBM.Data.DB2.DB2Connection]$DBconn
   [IBM.Data.DB2.DB2Command]$DBcmd
   [IBM.Data.DB2.DB2DataAdapter]$Adapter
   [System.Data.DataSet]$Data
   [boolean]$Connected

   DB2Adapter() {
   $this.Factory = [System.Data.Common.DbProviderFactories]::GetFactory(“IBM.Data.DB2”)
   $this.DBconn = $this.Factory.CreateConnection()
   $this.DBcmd = $this.Factory.CreateCommand()
   $this.DBcmd.Connection = $this.DBconn
   $this.DBcmd.CommandType = [System.Data.CommandType]::Text
   $this.Adapter = $this.Factory.CreateDataAdapter()
   $this.Adapter.SelectCommand = $this.DBcmd
   $this.Connected = $false
   }

   [void]Connect([string]$server, [string]$database, [string]$user, [string]$password) {
   If ($this.Connected -eq $false) {
   $string = $this.Factory.CreateConnectionStringBuilder()
   $string.Server = $server
   $string.Database = $database
   $string.UserID = $user
   $string.Password = $password
   $this.DBconn.ConnectionString = $string.ConnectionString
   try {
   $this.DBconn.Open()
   $this.Connected = $true
   } catch {
   Write-Host $_
   }
   }
   }

   [void]Close() {
   If ($this.Connected -eq $true) {
   $this.DBconn.Close()
   $this.Connected = $false
   }
   }

   [System.Data.DataRow[]]Run([string]$query) {
   If ($this.Connected -eq $true) {
   $this.DBcmd.CommandText = $query
   $this.Data = New-Object System.Data.DataSet
   $this.Adapter.Fill($this.Data)
   return $this.Data.Tables.Rows
   } Else {
   Write-Host “Not connected! Run the .Connect() method first”
   return [System.Data.DataRow[]]@()
   }
   }
   }

   ___________
   Example:

   $db2 = New-Object -TypeName “DB2Adapter”
   $db2.Connect(“192.168.1.20:50000”, “my_database”, “username”, “password”)
   $result = $db2.Run(“SELECT * FROM schema.table”)
   $db2.Close()

   Reply

   1. Glad you could use it and improve on it!

      Reply

8. Hi Luke, do you think there is a way I can run db2trc to a remote DB2 database?
   I desperately need to run a trace to a DB that I can only access through IBM Data Studio

   Reply

   1. I very much doubt it, unless you can code something to execute on the server using a programming language – which is likely to require ssh or other access. db2trc is an executable that works at a level far beyond what is possible at the db connection level. However, if you desperately needing to run it, then you’re probably working with IBM support – it is worth asking them if they have any suggestions.

      Reply

   2. As Ember said, db2trc is a standalone utility executable. You could check the Db2 .NET Data Provider documentation to see if there is anything in it that could get you what you are looking for, but thisc is a remote possibility.
      If you need to execute db2trc remotely from PowerShell (in which case you could gather and process its output locally on your workstation), you can look into PowerShell Remoting. Before you go too far down that path though, verify that your organization allows PowerShell Remoting to be used.

      Reply

9. I’m able to extract the data to a CSV file directly when I use $extractFile = ‘D:\TEST\Q_sampler_result_file.csv’ when I use Export-CSV to direct to $extractFile.

   What is wrong with how I’m using PSObject to bring this same table into an array which I would then, if successful direct to an HTML email?

   PS C:\Users\ME> $factory = [System.Data.Common.DbProviderFactories]::GetFactory(“IBM.Data.DB2”)
   PS C:\Users\ME> $cstrbld = $factory.CreateConnectionStringBuilder()
   PS C:\Users\ME> $cstrbld.Database = ‘dbname’
   PS C:\Users\ME> $cstrbld.UserID = ‘ME’
   PS C:\Users\ME> $cstrbld.Password = ‘PWD’
   PS C:\Users\ME> $cstrbld.Server = ‘AIXbox:50001’
   PS C:\Users\ME> $dbconn = $factory.CreateConnection()
   PS C:\Users\ME> $dbconn.ConnectionString = $cstrbld.ConnectionString
   PS C:\Users\ME> $dbconn.Open()
   PS C:\Users\ME> $dbcmd = $factory.CreateCommand()
   PS C:\Users\ME> $dbcmd.Connection = $dbconn
   PS C:\Users\ME> ########################################################################################################################################
   PS C:\Users\ME> #DEMONSTRATE –> showing count of how many failed work units in Application.PFKIWORKUNIT
   PS C:\Users\ME> #$dbcmd.CommandText = ‘SELECT COUNT(*) FROM Application.PFIWORKUNIT WHERE STATUS = 3’
   PS C:\Users\ME> #DEMONSTRATE –> showing an all selection from the Application.PFKIWORKUNIT
   PS C:\Users\ME> #$dbcmd.CommandText = ‘SELECT * FROM Application.PFIWORKUNIT WHERE STATUS = 4’
   PS C:\Users\ME> #DEMONSTRATE –> showing specific selections from the Application.PFKIWORKUNIT for STATUS 4
   PS C:\Users\ME> #$dbcmd.CommandText = ‘SELECT PFIWORKUNIT,ACTOR,FLOWDEFINITION,WORKTITLE,STATUS FROM Application.PFIWORKUNIT WHERE STATUS = 4’
   PS C:\Users\ME> $dbcmd.CommandText = ‘SELECT PFIWORKUNIT,ACTOR,FLOWDEFINITION,WORKTITLE,STATUS FROM Application.PFIWORKUNIT WHERE STATUS BETWEEN 0 AND 3’
   PS C:\Users\ME> #$dbcmd.CommandText = ‘SELECT PFIWORKUNIT,ACTOR,FLOWDEFINITION,WORKTITLE,STATUS FROM Application.PFIWORKUNIT WHERE STATUS BETWEEN 0 AND 9’
   PS C:\Users\ME> ########################################################################################################################################
   PS C:\Users\ME> $dbcmd.CommandType = [System.Data.CommandType]::Text
   PS C:\Users\ME> $da = $factory.CreateDataAdapter()
   PS C:\Users\ME> $da.SelectCommand = $dbcmd
   PS C:\Users\ME> $ds = New-Object System.Data.DataSet
   PS C:\Users\ME> $rowCount = $da.Fill($ds)
   PS C:\Users\ME> $dbconn.Close()
   PS C:\Users\ME>
   PS C:\Users\ME> write-host $rowCount
   10 ##
   PS C:\Users\ME> if($rowCount -gt 0) {
   >>
   >>
   >> $exportObject=@()
   >> $Object = New-Object PSObject
   >> $ds | ForEach{
   >> $Object | Add-Member -MemberType NoteProperty -Name “PFIWORKUNIT” -Value $row.PFIWORKUNIT
   >> $Object | Add-Member -MemberType NoteProperty -Name “ACTOR” -Value $row.ACTOR
   >> $Object | Add-Member -MemberType NoteProperty -Name “FLOWDEFINITION” -Value $row.FLOWDEFINITION
   >> $Object | Add-Member -MemberType NoteProperty -Name “WORKTITLE” -Value $row.WORKTITLE
   >> $Object | Add-Member -MemberType NoteProperty -Name “STATUS” -Value $row.STATUS
   >> $exportObject += $Object
   >> }
   >>
   >>
   >> write-host $exportObject
   >> }
   >>
   @{PFIWORKUNIT=; ACTOR=; FLOWDEFINITION=; WORKTITLE=; STATUS=} ##<– listing out ONLY Headers of Columns

   Reply

   1. Sorry for a late response Danny, I missed the notification of your post somehow.
      At first glance:
      1) You are building an object $Object to hold the various properties contained in $row, but you are building only a single instance of it. If there are multiple rows returned, your script will abort because it would try to add an already existing property to the $Object instance. You need to create a new $Object for every $row that will be processed – the “$Object = New-Object PSObject” statement has to be inside your ForEach-Object loop.
      2) Where does the $row variable come from? It isn’t clear from the code you included…
      3) If you are wondering specifically why the output of “@{PFIWORKUNIT=; ACTOR=; FLOWDEFINITION=; WORKTITLE=; STATUS=} ##<– listing out ONLY Headers of Columns" are only the column headers, then it is because the statement @{PFIWORKUNIT=; ACTOR=; FLOWDEFINITION=; WORKTITLE=; STATUS=} will create a new hashtable with just the five named elements in it, but without any values assigned to them. See https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_splatting for more about that.

      Reply

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