I have been a Microsoft SQL Server fan since late 1996 using SQL Server 6.5.  Since that time, SQL Server has involved and Microsoft has released versions 7.0, 2000, 2005, 2008, and 2008 R2.  Microsoft even provides an Express Edition that is a scaled down free edition that includes the core database engine. The Express edition is limited to using 1xCPU and 1GB memory, along with 4GB databases.   SQL Server Express 2008 R2 supports 10GB databases.  The Express editions was intended to replace the outdated MSDE editions.

For the last fifteen years (I’m feeling old now), I have used it for both professional and personal projects and find it both feature rich, intuitive to use, and offering great database performance. And if you’re a Microsoft .NET developer, it fits and-in-glove with other Microsoft products.

Unfortunately, for the past few years I have been forced to use DB2 as more of our internal .NET products and applications leverage it over SQL Server.  This is primarily due to cost versus any other value-add from DB2.

One of the challenging tasks I had recently was jumping around between multiple DB2 servers and running particular scripts against certain databases and reviewing the output for product setup or configuration issues.   One of the problems I faced was reviewing the output between this separate reports.   It was horrible due to the primary identifier between tables was a GUID.   This required me to compare the GUID from one output to the same GUID on another output for hundred of records.    This was promise was very time consuming and labor intensive, to say the least.

Since it was my turn and performing the verification of the setup and configuration, I had to run through the manual process of running SQL scripts and manually reviewing the output.   After spending a few hours on this, I was determined I’d never do this again…  

Powershell to the rescue!

Now, I’m not a Powershell guru, but do know enough to be dangerous.   In case you’re not familiar with Powershell, it’s basically a new command shell environment similar to Unix shell environments, but on steroids.    It leverages the entire .NET framework and can virtually do anything you can do in Unix, if not more.   Gone are the days of using BAT files and VBScript – unless you’re stuck with having machines without Powershell installed.

The first thing I needed to do was automate the SQL scripts as much as possible.   The first task was to create a .CSV file of all the data so it could be later read by Powershell.    Luckily for me, DB2 includes a built in EXPORT function.

From the command line, you can invoke a DB2CMD to execute your SQL Script.   Here my script is called Customer_Verification.db2.

DB2CMD –c –w db2 –tf Customer_Verification_Export.db2 –z Customer_Verification_Export.out

The output from this command, such as number of rows being exported or any errors, would be dumped to a file named Customer_Verification.out.

The Customer_Verification_Export.db2 script contains the SQL statements:

connect to Customer user DB2ADMIN using Pa55w0rd01;
EXPORT TO ‘C:\\TEMP\\Customers.csv’ of del
SELECT I.CUSTID, I,CUSTNBR, I.CUSTNME
FROM DBO.CUSTOMER
ORDER BY I.CUSTNBR
connect reset;
terminate;

The above creates a nice comma-delimited CSV file named Customer.csv under C:\TEMP folder.

Second, I would hop over to my other DB2 server and run another SQL script against a different database.

Here is another script called GroupsRoles_Verification.db2.

DB2CMD –c –w db2 –tf GroupsRoles_Verification_Export.db2 –z Details_Verification_Export.out

The GroupsRoles_Verification_Export.db2 script contains the SQL statements:

connect to Details user DB2ADMIN using Pa55word01;
SET SCHEMA DBO;
EXPORT TO ‘C:\\TEMP\\Groups.csv’ of del
SELECT CUSTID, DISPLAYNME
FROM Groups
ORDER BY ID.CUSTID;

SET SCHEMA DBO;
EXPORT TO ‘C:\\Roles.csv’ of del
SELECT CUSTID, ROLESETUP
FROM Roles
ORDER BY ID.CUSTID;

connect reset;
terminate;

After running the two scripts, I have three CSV files, Customer.csv, Groups.csv, and Roles.csv.   Since the last two files only reference a customer by a GUID and I need to pull in the customer name from customers table and JOIN the files together in one nice report, Powershell seemed like the perfect fit.

The script below reads the customer.csv file into variable.  It then reads the groups.csv and roles.csv file into a hash table.  I then iterate thru the customers table using a ForEach-Object statement and where either a CUSTID from the groups.csv file or CUSTID from the roles.csv file matches, it will add columns to pipeline and display the result in a table.

 $customer = Import-Csv C:\TEMP\Customer.csv -Header CUSTID, CUSTNBR, CUSTNME
 $groups_ht = Import-Csv C:\TEMP\Groups.csv -Header CUSTID, DISPLAYNME | Group-Object CUSTID -AsHashTable
 $customer | ForEach-Object {
    if($groups_ht.ContainsKey($_.CUSTID)) {
       $droups_ht.($_.CUSTID) |
          Add-Member NoteProperty CUSTNBR $_.CUSTNBR -PassThru |
          Add-Member NoteProperty CUSTNME $_.CUSTNME -PassThru
       }
    } | Format-Table CUSTNBR, CUSTNME, DISPLAYNME -Autosize

$customer = Import-Csv C:\TEMP\Customer.csv -Header CUSTID, CUSTNBR, CUSTNME
$roles_ht = Import-Csv C:\TEMP\Roles.csv -Header CUSTID, ROLESETUP | Group-Object CUSTID -AsHashTable
$customer | ForEach-Object {
   if($roles_ht.ContainsKey($_.CUSTID)) {
      $roles_ht.($_.CUSTID) |
         Add-Member NoteProperty CUSTNBR $_.CUSTNBR -PassThru |
         Add-Member NoteProperty CUSTNME $_.CUSTNME -PassThru
   }
} | Format-Table CUSTNBR, CUSTNME, ROLESETUP -Autosize

Now this script works much better than the old method of producing the output and reviewing.   However, it still requires a bit of moving around from server to server to get the correct files.    There are still some improvements I can make to the script, such as prompting for database name, removing the dependency of the SQL script and dynamically creating the SQL script each time on-the-fly, and perhaps trying to run this script from one server and remotely connecting to each database server…. using DB2’s CATALOG and ATTACH commands.  

If you’re using DB2 and having to do a bunch of monkeying around with output files between servers, I hope this script provides some assistance in this area.

Other posts of interest

• No Related Post