A Few Scripting Basics with DB2 and PowerShell

You may also like...

8 Responses

  1. Luke Numrych says:

    Ember –
    It might be “better” to use the ADO.NET IBM.Data.DB2 provider rather than the OleDb provider in PowerShell. It seems to me that it may provide a better integration with DB2 and would be a more direct way of getting to DB2 API as opposed to the OleDb provider. It is what IBM suggests (http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.swg.im.dbclient.adonet.doc/doc/c0024474.html) when coding in .NET, and since PowerShell’s nickname could just as well be “.NET shell”…

    The example included in the KnowledgeCenter article is specific to C#; the syntax is a little different for PowerShell to get an instance of the DB provider factory.
    Instead of:
    DbProviderFactory factory = DbProviderFactories.GetFactory(“IBM.Data.DB2”) /* C# syntax */
    $factory = [System.Data.Common.DbProviderFactories]::GetFactory(“IBM.Data.DB2”) /* Powershell Syntax */

  2. Luke Numrych says:

    Here is an additional link to IBM’s KnowledgeCenter, showing their support for ADO.NET (and pitching its benefits):


  3. Luke Numrych says:

    I have spent some more time with the ADO.NET IBM.Data.DB2 provider, and there are some things I’ve discovered with respect to error handling that might be share-worthy. All code below is using the ADO.NET IBM.Data.DB2 provider; however, I believe that the approach may be extendable to the “OleDb provider way” and may give you similar results.

    For example, given the statement “SELECT BLA, TABSCHEMA, TABNAME FROM SYSCAT.TABLES WHERE COMPRESSION IN (‘R’, ‘B’)”, and the code to execute it:


    What is assigned to $err after the error is thrown is an instance of a System.Management.Automation.ErrorRecord.

    This class has a property Exception which returns the actual Exception object that was thrown when the error was detected. When using the ADO.NET provider, the exception is of type IBM.Data.DB2.DB2Exception (http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.swg.im.dbclient.adonet.ref.doc/doc/DB2ExceptionClass.html), and has some interesting properties:

    $_.Exception.Message – “ERROR [42703] [IBM][DB2/NT64] SQL0206N “BLA” is not valid in the context where it is used.”
    $_.Exception.Errors – this is a collection of objects of type IBM.Data.DB2.DB2Error, with only one element in this example. This is what can be seen in $_.Exception.Errors[0]:

    Message : [IBM][DB2/NT64] SQL0206N “BLA” is not valid in the context where it is used.
    SQLState : 42703
    NativeError : -206
    Source : IBM.Data.DB2
    RowNumber : -1

    So the native DB2 error code can be captured either from the error description via regex like you have shown, or it can be obtained from the NativeError property of the IBM.Data.DB2.DB2Exception object. Unfortunately, it *still* does not provide the SQLCA… The documentation suggests that SQLCommunicationsArea should really be a property of a statement (DB2Command) or the connection itself maybe, not necessarily of an error or an exception; however, it is missing there as well.

  1. April 14, 2015

    […] A Few Scripting Basics with DB2 and PowerShell […]

  2. January 5, 2016

    […] A Few Scripting Basics with DB2 and PowerShell […]

Leave a Reply

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