locked
Interesting Enterprise-wide Data Access Problem RRS feed

  • Question

  • Interesting Enterprise-wide Data Access Problem

    Scope: How to determine what connections from Windows to iSeries are going to fail ahead of a big iSeries changeover?

    We have an AS400 AKA iSeries AKA system i that is going to get a SAN connection soon. The SAN connection goes through an IBM thing called an IASP.

    Currently, there is a single database on the iSeries that has the same name as the iSeries system name. We'll refer to the iSeries system name as myiSeries and the single database myiSeries.

    On the iSeries a new database with a new previously unused name must be created, and user libraries, tables, etc. will be moved into this new database. We'll refer to the new database as NewDB.

    The original database named myiSeries on the iSeries named myiSeries will remain, containing some system libraries, tables, etc.

    We have connections to iSeries DB2 (aka DB400 in older terminology) originating from the Windows distributed platform side of the house from servers, desktops, and laptops from all over our U.S. WAN.

    The issue arises from the fact that applications' use of a connection definition (connection string parameters or connection object properties) may include a specific iSeries database AKA RDB AKA RDBName to connect to and use.

    If no iSeries database is specified in the connection, then the iSeries system default database will be used. This default iSeries database is apparently a property of an iSeries job that is created to service an incoming iSeries DB2 SQL request.

    Two scenarios :
     No specific database requested in connection definition (iSeries system default database as per job defaults used) - not an issue prior to SAN/IASP implementation or afterwards
     Specific database is requested in connection definition - not an issue prior to SAN/IASP implementation, afterwards will fail because all the data is in new iSeries database NewDB now

    Prior to SAN/IASP implementation, only one database with the same system name as the iSeries existed, so specifying that database was not required but was not an issue as that is where all the data existed.

    The question is :

    How to locate any Windows applications that are specifying the iSeries database in their connection across the entire enterprise ?

    We are considering the following :

    Source code reviews, scanning source for connection string definitions in Visual Source Safe and Team Foundations server, scanning source code folders for files containing connection strings, etc. This only addresses the places we know to look for source code/ASP config files, etc. and does not address the applications running in places we do not know to look.
    Scanning folders for files containing connection strings for Java applications, does not address Java applications that do not store well-formed connection strings in files.
    File or source code searching would only turn up well-formed connection strings, identifying connection definitions that are built at run-time may not be possible by searching.
    Tools such as AVICode could potentially extract some use of the iSeries DB2/DB400 drivers from dotNet applications running on servers, but cost of licensing use for all servers, desktops and laptops is prohibitive, and does not address legacy Visual Studio or Java applications.
    The iSeries DDM or DRDA TCP/IP server for DB2/DB400 SQL connections listens on port 447 (the well-known DDM port) and 446 (the well-known DRDA port) as well as 448 (the well-known SSL port), so network sniffing through routers and switches for connections to the iSeries DB2/DB400 from the Windows clients may yield the IP addresses of the clients, but does not address whether the connection is requesting a specific iSeries database.
    Connections initiated from SQL Server Linked Servers, DTS packages, and SSIS packages are scannable for connection details with a little bit of effort, this may not be very problematic.

    I am most interested in figuring out how to analyze this in a comprehensive fashion as the applications are running. I feel that we must find the applications that need to be reconfigured, then find the source code/configuration files for those applications.

    What approaches for the Windows platform could be used across every server, desktop, and laptop in a geographically distributed WAN environment to get as close to 100% knowledge of where the client applications are running that are requesting a specific iSeries database?

    We are going to approach IBM for possible solutions on the iSeries side, so I am trying to get a handle on what, if anything, can be done on just the Windows side to address the issue.

    Windows servers are all 32-bit Windows 2000/2003
    Windows desktops and laptops are all 32-bit Windows XP/Vista
    iSeries is OS/400 v5r3

    Thanks in advance for any advice or perspective on this issue!



     

    Some examples and detail below

    Successful Simple Example in vbscript using System i Access for Windows OLE DB Provider :
    No specific database requested in connection string (iSeries system default database as per job defaults used) - not an issue prior to SAN/IASP implementation or afterwards

     dim Connection
     dim ConnectionString
     dim sqlstmt
     dim SQLresponse

     Set Connection = CreateObject("ADODB.Connection")
     
     ConnectionString="Provider=IBMDA400;Data Source=myiSeries;Default Collection=MyLibrary;"
     Wscript.StdOut.WriteLine ConnectionString
     Connection.Open ConnectionString
     sqlstmt = "SELECT COUNT(*) as cnt FROM MyLibrary.MyTable"
     Wscript.StdOut.WriteLine sqlstmt
     set SQLresponse = connection.Execute(sqlstmt)
     Wscript.StdOut.WriteLine SQLresponse("cnt")

     Wscript.StdOut.WriteLine
     set SQLresponse=Nothing
     set Connection=Nothing
     
    Failing Simple Example in vbscript using System i Access for Windows OLE DB Provider :
    Specific database is requested in connection string - not an issue prior to SAN/IASP implementation, afterwards will fail because all the data is in new iSeries database NewDB now

     dim Connection
     dim ConnectionString
     dim sqlstmt
     dim SQLresponse

     Set Connection = CreateObject("ADODB.Connection")
     ConnectionString="Provider=IBMDA400;Data Source=myiSeries;Default Collection=MyLibrary;Initial Catalog=myiSeries;"
     Wscript.StdOut.WriteLine ConnectionString
     Connection.Open ConnectionString
     sqlstmt = "SELECT COUNT(*) as cnt FROM MyLibrary.MyTable"
     Wscript.StdOut.WriteLine sqlstmt
     set SQLresponse = connection.Execute(sqlstmt)
     Wscript.StdOut.WriteLine SQLresponse("cnt")

     Wscript.StdOut.WriteLine
     set SQLresponse=Nothing
     set Connection=Nothing


    The error we wish to avoid seeing for applications after the SAN/IASP implementation looks like this :
     IBMDA400 Command: SQL0204: MyTable in MyLibrary type *FILE not found.


    iSeries DB2/DB400 connections are implemented in various programming languages for In-House systems and 3rd-Party Vendor systems including

     VB6
     classic ASP
     dotNet v1.x, v2.x, and v3.x
     Java
     C++
     SQL Server 2000 DTS packages
     SQL Server 2005/2008 SSIS packages
     SQL Server 2000/2005 Linked Servers


    iSeries DB2/DB400 data drivers on the Windows platform currently in use include :

     IBM Client Access OLEDB driver AKA System i Access for Windows OLE DB Provider
     IBM Client Access dotNet driver
     HiT OLEDB/400 driver
     Microsoft OLE DB Provider for DB2 (for SQL Server 2005 & 2008)
     DataDirect Connect for ADO.NET Data Provider for DB2
     IBM Toolbox for Java JDBC AS400 driver



     

    Tuesday, March 24, 2009 4:08 PM

Answers

  • Hi

    From what I understood, your org needs kind of monitoring system for client applications that perfom operations on iSeries database. Your org is using clients written in different programming languages, which means each one is using different set of iSeries drivers/components to communicate with the server.

    I am not aware of any system like that, but I do have some ideas that might help.

    1.  using generic network monitoring tools
    If all iSeries databases in your org are listening on the same port (or on specific set of well-known ports), you can use generic network monitoring tools that reside either on client computer, on the database itself or on the routers/gateways/firewalls in between. If you find such network monitoring system and this system supports centralized monitoring, you can use it to monitor all the traffic to these well-known ports.

    2. using database-specific monitoring or logging tools
    If iSeries database has monitoring system built-in or as external tool, consider using it as well. Similarly, if iSeries database has logging system and this logging system keeps tracks of client IP address, consider enabling this logging and let it run for a while. Later, scan the logs to collect all the IP addresses that were connecting to this database and using network tools identify all client computers.

    Unfortunately, I am not familiar with this database so I cannot give more information.

    Regards,
     Nissim

    Postings are provided "As Is" with no warranties and confer no rights.
    Friday, April 3, 2009 11:07 PM