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

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.
LVL 14
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
LinInDenverAuthor Commented:
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).
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LinInDenverAuthor Commented:
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.
LinInDenverAuthor Commented:
Much thanks, Richard!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.