Mainframe Database Connectivity

    Mainframe, also referred to as Legacy or Host Systems, are not like client/server database systems. Actually they are, but they're not.

     

    Prior to talking about Host Systems, here’s a quick description of RDBS (Relational Database System) systems and communication – note that the following is not touching on creating web services for communication to database servers:

     

    On a pure MS Windows environment or a mixed Windows/UNIX environment, if you install a database such as MS SQL Server, or Oracle, or any client/server RDBS, you are going to get two pieces of software:

     

    There’s Client software, which is to be installed on any Windows client machine that is to connect to a database system - such as your local desktop or laptop.  You can install the Client software on multiple machines that may need to connect to the database server.


    Then there's the Server software. This is installed once, and once only (when not taking into account database clustering), onto a dedicated Windows or Unix based server. All communication to this Server software is brokered via the databases Client software.  So if you have Oracle database Server on a UNIX box, you can still install Oracle’s Client Software onto your windows Desktop and the Client Software will manage communication to the Server – as long as your network is configured for your Windows machine to see the UNIX machine, there’s nothing really too complicated that you have to do to get this working.

     

    In the case of QlikView, and any other product you may be using that wants to talk to a database server to "pull" for data, communication must be managed via the database's client software. To talk to the Client Software, QlikView (or any other product), must connect via ODBC or OLEDB.  This connectivity driver is normally provided by the database manufacturer.

     

    IE:

    From your local desktop or laptop: QlikView -> OLEDB or ODBC -> MS SQL Server (Client Software) ->

    To the Database Server: MS SQL Server (Server Software).

     

    For the Host System, there really is no client software.  There is also no OLEDB or ODBC driver.  None that I’ve ever seen anyway.  This makes it difficult for a “data-out” tool such as QlikView, or any other product, to fetch data from the Host System. 

     

    So how do you connect?  If you see the Host system as a “data island”, there is a way to get data from that Host system; but it means you'll need to use a bridge.

     

    Other people have already built a bridge, such as Microsoft.  They have a tool called Host Integration Server.
    This is essentially MS SQL Server that connects to your Host system and pulls the data into MS SQL Server.  You would then just connect to MS SQL Server as per the earlier discussion of Client/Server connectivity.
    http://www.microsoft.com/biztalk/en/us/host-integration.aspx.  Contact Microsoft for more information and best practices on use of this product.

     

    There could be other tools, similar to Microsoft’s Host Integration Server, that can do this as well.  You’d have to search for them, but as the above eludes to, what you will have installed is new infrastructure to manage your data from your Legacy or Host System.

     

    There could be other options though – haven’t tested this but just some thoughts.

     

    First option – use what you got.  If on your mainframe, the database server is IBM DB2, IBM may have a DB2 database Client for Windows that can manage communication to the DB2 Server on the mainframe.  I’m spit-balling here, I don’t know if this would work but IBM may have already figured this communication piece out.  If this is what you have, confirm with IBM that this would work and you could be up and running fairly quickly.

     

    The second option is for you to do your own custom work – caution here is that this would essentially be a very low-level form of ETL, very manual, and could become painful for you to manage over time. 

     

    Custom work means you would have to create COBOL applications that would essentially perform ETL for you.  Your COBOL program would output data from your Mainframe database to .CSV files.  You would then have to  FTP these .CSV files over to a Windows machine where QlikView could connect to.  This process could become unwieldy very fast as you could have multiple COBOL programs, change process that you would have to manage, having someone working on this (that knows COBOL), etc…  

     

    To sum up, can QlikView connect to a Mainframe system and pull in data?  Yes, but it would have to be one of three possible ways:

     

    1. Use a solution such as Microsoft’s Host Integration Server as a bridge.
    2. If it’s IBM’s DB2 database server on the mainframe, IBM may have a solution for you to use their Client software to connect to the database Server on the Mainframe.
    3. Build your own process flow.