Anyone used MS SQL Server with a DB2 database (mine is an ERP app called Infinium)

Posted on 2008-11-10
Last Modified: 2012-05-05
Hello there,

My company owns MS Reporting Services and I am trying to find out if anyone has had success connecting it to a DB2 system for reporting? My specific system is an HR/ERP called Infinium. I've had trouble getting ODBC connections into it from any other application except one called Strategy (a querying app), but I don't know if that is specific to my application or to DB2.

Thanks for any information you have.
Question by:LinInDenver
    LVL 3

    Expert Comment

    I have connected my PC applications to our DB2 database here at work.  We have iSeries Navigator software which includes an ODBC driver for DB2.  That driver is used to create system DSNs which can then be used for the ODBC connection.  I also have a Java based database software that uses a Java ODBC driver.  That application is also able to connect successfully to db2.
    LVL 14

    Author Comment

    Hi Richard! Thanks for this information.

    I have found iSeries Navigator installed on my PC and I've spent a little time in it. it is strange, I am able to query against a file from inside the navigator (called hrdbfa.prpms) but cannot find it through the navigator browser anywhere - any tips on how to locate a file (search or find functions) so I can see its properties inside Navigator?

    I created an iSeries ODBC system DSN odbc connection and attempted to link into the tables (through MS Access Linked Tables). I was able to connect to one file, but the other one gave me an error referencing "too many indexes" and failed to connect. Have you ever had that error before and if so were you able to resolve it? I'm not even sure where to see indexes or keys on the file structures (my sys admin refers to this as a "file" and not a "table" - when I look in the INDEXES section in iSeries Navigator nothing is displayed).
    LVL 3

    Accepted Solution

    I am glad that this information is proving useful to you.   :)

    The "too many indexes" problem with the MS Access is a problem that we recently encountered here at my work.  If an AS400 physical file has more than 32 logical files against it MS Access will not link to it.  This is a MS Access limitation that I have not yet found a solution for.  If you really need to link MS Access to such a physical file you will probably have to make a separate (and maybe kept in sync) physical file that does not have that many indexes.  Alternatively, you could remove some of the logical files against the current physical file so that there are 32 or less.

    As for the file hrdfa.prpms, what type of file is it?  Is it a normal physical or logical file?

    Also, now that I think about it, do you have a direct login to the AS400 "green screen"?  If you are not familiar with or have never actually worked directly on an AS400 then you may have to enlist some help at  your work from an AS400 DB2 person or an AS400 developer to help get some of the answers we need.

    Let me know what other information you may need.  I or some other experts here will be glad to help.
    LVL 14

    Author Comment

    Thanks Richard!!! the PRPMS is a physical file, we found with no keyed fields at all (so perhaps it thought it was a file with 200 indices!). I asked our admin about this and she told me to try PRLMS which is a logical view with two keyed fields. It linked into Access with no issue!!

    Thanks so much.
    LVL 14

    Author Closing Comment

    Much thanks, Richard!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
    Hello, In my precious Article  ( saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now