PowerShell for DB2 DBAs – Part 1

Luke Numrych

Any DBAs faced with having to administer and maintain a large number of instances and databases will quickly find themselves swamped with work unless they learn to script most of their workload.  Of course, the benefits of scripting do not end there.  This post is intended to be the first of a planned series with a goal to show how PowerShell can be used by a Windows DB2 DBA for automating tasks of administration and operation of DB2 instances and databases.  Most of the techniques discussed can be easily adapted for any number of other purposes. I will discuss techniques and share tips and experience gained from my own practice.

Background

A tiny, little bit of background first.

A DBA working with DB2 on any *ix system usually has some very good choices of scripting shells and a very comprehensive set of tools (sed, awk, and grep to name a few) provided by the operating system.

A DBA working with DB2 on Windows used to be reduced to using DOS shell and batch scripting – a relic of the dark ages of computing that required a lot of ingenuity and sheer will-power in order to achieve the goals of automating the workload.  DOS batch scripts that are attempting to do anything more complicated than “db2 –tvf runthis.sql” can quickly become very convoluted and hard to maintain.  Not only is the batch script language very limited; the set of command line tools suitable for scripting available out-of-the-box in a Windows OS used to be severely lacking as well (c’mon, no grep?).  Some relief came with the introduction of Windows Scripting Host and the JScript and VBScript languages that were provided with it by default. That solution brought its own problems – it is not integrated with the shell, and it has the potential of being a security vulnerability and thus installation of it is often avoided by Windows OS administrators.  Additionally, the development on Windows Scripting Host has now been suspended by Microsoft – and for a good reason, as the replacement is much better.

With the introduction of PowerShell, Windows gained a very strong scripting solution that can easily compare to anything available on an *ix system.  It is a fully featured object-oriented scripting language that is very tightly integrated with the Microsoft .NET Framework and the Windows OS.  It takes the familiar concept of pipelining – the passing of the result of one operation as input to the next operation – to another level by operating not just on strings of bytes, but on whole objects (for readers not used to working with objects – more on this later).  Starting with Windows 2008 R2, PowerShell is included in the default system installation, which means that, unlike in the case of add-on packages and third-party tools, a Windows DB2 DBA can count on it being available on every vanilla OS installation.

So how would a DB2 DBA start using PowerShell to their advantage?

Let us begin by exploring several methods for connecting to a database and executing a simple select statement.

Note: The examples shown below use hardcoded credentials – this is for brevity and clarity of code for illustration purposes only.  Please DO NOT do that in your scripts. In a future installment I will explore the usage of encrypted credentials in PowerShell scripts.

 

Method 1: The “classic” method, by invoking the DB2 CLP and passing it the commands to execute.

Example:

db2 “connect to dbname user dbuser using dbpwd”
db2 “select tabname from syscat.tables where not tabschema like ‘SYS%’ WITH UR”

This is the simplest method; however, trying to execute even such simple commands in a PowerShell session without preparing the environment will result in a message “command line environment not set up”.   There are several good posts on the internet on how to set up the db2 command line environment in PowerShell, and how to make the changes stick in the user profile so that they do not have to be re-done every time a new PowerShell session is started, so I will not get into the details here.

Usage scenarios:

  • Any administrative commands that can be interpreted and executed only by DB2 CLP.
  • Commands executed manually from the shell – for example when testing, diagnosing or correcting a problem.
  • To obtain the SQLCA information, the CLP can be forced to provide it by using the -a CLP option, and the resulting output can be parsed using regular expressions.
  • Scripts created for a very specific or one-off task when the goal is to write the script quickly and make it very simple.

Disadvantages:

  • It requires, at minimum, a DB2 Runtime Client installation on the machine on which it is being invoked.
  • If targeted at databases residing on remote systems, remote nodes and databases first have to be locally cataloged.
  • Output is in the form of basically lines of text, which is not always convenient or most efficient to process.
  • In case of errors, the DB2 CLP returns only a success/warning/error/system error code back to the shell.  The actual DB2 error number and description can of course be displayed (or, to be specific, output to the standard-output file).  This additional information can of course be captured by using regular expressions; however, it is an additional step.

 

Method 2: The “control-freak” approach, by creating a separate process.

This is a more complex way to execute CLP commands (or any other commands and programs for that matter).  The advantage of it is that it provides fairly complete control of the process, gives easy access to the process vitals (CPU, memory, status, standard files…), and makes it very easy to start separate processes that can be executed in parallel.

Example:

$prc = New-Object System.Diagnostics.Process
$psi = New-Object System.Diagnostics.ProcessStartInfo
$psi.FileName = “db2”
$psi.Arguments = “connect to dbname user dbuser using dbpwd”
$prc.StartInfo = $psi
$prc.Start()
$prc.WaitForExit()
$prc = New-Object System.Diagnostics.Process
$psi.Arguments = “select tabname from syscat.tables where not tabschema like ‘SYS%’ WITH UR”
$prc.StartInfo = $psi
$prc.Start()
$prc.WaitForExit()

Usage scenarios:

  • Any commands that can be interpreted only by DB2 CLP like those that do not have an ADMIN_CMD procedure equivalent.
  • To obtain the SQLCA information, the CLP can be forced to provide it by using the -a CLP option, and the resulting output can be parsed using regular expressions.
  • Parallel execution of commands by creating individual db2 processes.
  • Gives complete control over the executing process. Also provides for easy monitoring of it and for gathering useful execution data like processing time, memory used.
  • Standard input/output/error can be redirected easily and handled in different ways.
  • If wrapped into a function, it can be used as a building block of a standardized DB2 command execution library that can be then reused many times in different scripts. Code reuse is a good thing.

Disadvantages:

  • It requires, at minimum, a DB2 Runtime Client installation on the machine on which it is being invoked.
  • If targeted at databases residing on remote systems, remote nodes and databases first have to be locally cataloged.
  • Output is in the form of basically lines of text, which is not always convenient or most efficient to process.
  • In case of errors, the DB2 CLP returns only a success/warning/error/system error code back to the shell. The actual DB2 error number and description can of course be displayed (or, to be specific, output to the standard-output file).  This additional information can of course be captured by using regular expressions; however, it is an additional step.
  • More complex than the first method.
  • BUGS! Some commands seem to fail to exit the DB2 CLP process, which can cause the script to never end.  I have observed this in PowerShell 3.0, DB2 WSE 10.1 FP4 while using SYSPROC.ADMIN_MOVE_TABLE and opened a PMR with IBM – not yet resolved as of 2015-07-27.

 

Method 3: The “my God, it is full of objects!” method – by using ADO.NET and the IBM Data Server Provider for .NET.

Since PowerShell is an object-oriented scripting language, it is important to be familiar with the most basic concepts of object-oriented programming.  Very shortly, for readers unfamiliar with the distinctions between classes and objects – think of a class as an idea or concept, and an object – sometimes called an instance (of a given class) – as the realization or manifestation of that idea.  For example, you and I have the same concept of a “car class” – it has properties such as color, number of doors, brand and model, and it has methods such as drive.  However, the cars that we individually own – the “objects of car class” – are not the same because they differ in color, brand, model, and other properties.  Even if their properties match exactly (the objects are equivalent) because we own the same brand, model, and color of a car, the objects themselves are considered different if they cannot occupy the same physical location (or in computer science – same memory location) at the same time (baring a car crash, or memory corruption of course…).  Therefore, if I change the color property of my instance of a car by painting it, the color property of your instance of a car will remain unchanged.

A few additional words about the relationship of PowerShell and .NET.  As I mentioned in the introduction at the beginning of the post, PowerShell is very tightly integrated with .NET; in fact, .NET is the platform on which PowerShell is built.  The two go hand-in-hand: you cannot have PowerShell without .NET.  This integration gives PowerShell access to all of the classes that are available in .NET, including a set of data access classes called ADO.NET.  ADO.NET talks to data sources (databases for our purposes) by way of objects of one of the data provider classes. There are two basic data provider classes included by Microsoft in ADO.NET: an OLE DB provider and an ODBC provider, both of them serving as bridges to the respective IBM-developed provider drivers.  While either of them can be used to access DB2-hosted databases in a manner similar to the one shown in the example below, IBM does not recommend using them.  Instead, IBM has created a native DB2 Data Server Provider for .NET that is included in every IBM DB2 Data Server Driver Pack or Client installation with the exception of Driver for JDBC and SQLJ or Driver for ODBC and CLI.

Example:

$factory = [System.Data.Common.DbProviderFactories]::GetFactory(“IBM.Data.DB2”)
$cstrbld = $factory.CreateConnectionStringBuilder()
$cstrbld.Database = “dbname”
$cstrbld.UserID = “dbuser”
$cstrbld.Password = “dbpwd”
$cstrbld.Server = “host:port”
$dbconn = $factory.CreateConnection()
$dbconn.ConnectionString = $cstrbld.ConnectionString
$dbconn.Open()
$dbcmd = $factory.CreateCommand()
$dbcmd.Connection = $dbconn
$dbcmd.CommandText = “select tabname from syscat.tables where not tabschema like ‘SYS%’ WITH UR”
$dbcmd.CommandType = [System.Data.CommandType]::Text
$da = $factory.CreateDataAdapter()
$da.SelectCommand = $dbcmd
$ds = New-Object System.Data.DataSet
$da.Fill($ds)
$dbconn.Close()

Usage scenarios:

  • Anytime for any task as long as it can be executed by invoking a SQL command or calling a procedure or function.
  • Can be used to develop a standardized DB2 command execution library by creating functions and parametrizing variables.
  • Parallel execution of commands.
  • Whenever output is meant to be further handled or analyzed programmatically. By using this method what we can get back from DB2 are not just unstructured strings of data – we can get OBJECTS!  And objects are FUN!  And much easier to handle programmatically…

Disadvantages:

  • While this approach can be used directly from command line, due to its complexity, it would be most likely utilized in something reusable, like function in a script.
  • IBM DB2 Data Provider for .NET driver needs to be installed and registered with .NET framework on the machine on which this method is used.
  • There does not seem to be a way to obtain the SQLCA data when using this method – I am still searching for it.

 

In the next installment of PowerShell for DB2 DBAs we will take a closer look at handling errors and output in the case of each of the access methods described above.

 

Happy scripting!

 


Luke Numrych Luke Numrych has over 18 years of experience in various IT fields, ranging from mainframe operations to software development. He has been involved in supporting DB2 LUW on Windows since the beginning of 2011, and has been a production support DBA for DB2 databases in an OLTP environment since March of 2012. Luke is a member and the Secretary for 2015 of the Wisconsin DB2 Users’ Group.
Luke’s Linked-In profile is https://www.linkedin.com/in/lukenumrych, and he can be reached via email at l.numrych@gmail.com.
 
 


You may also like...

5 Responses

  1. Glenn Brennan says:

    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)

    • Luke Numrych says:

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

  2. Glenn Brennan says:

    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

    • Luke Numrych says:

      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.

  1. August 11, 2015

    […] PowerShell for DB2 DBAs – Part 1 […]

Leave a Reply

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