• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

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.
0
LinInDenver
Asked:
LinInDenver
  • 3
  • 2
1 Solution
 
richard_cristCommented:
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.
0
 
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).
0
 
richard_cristCommented:
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.
0
 
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.
Lin
0
 
LinInDenverAuthor Commented:
Much thanks, Richard!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now