SQL Connection to iSeries?

[mrfields]

Hello,
I'm an accountant wanting to supply our IT dept with info on how to connect MS SQL Express to JDE. My research of this topic seems to show it's not an easy task. If anyone can provide some good info that I can pass on to the IT staff, I'd really appreciate it. This would benefit us highly. Here's the info about our IBM server and JDE program:
Model 520
OS version V5R4
JDE version A73.7 of World
Processor feature 7543

 

[mnorwood]

Why are you trying to connect the SQL databse to JDE? What are you trying to accomplish?

 

[mrfields]

We want to use Microsoft sql to query jde data on the iseries. Currently, our end users are using a poor quality sql program called Viewpoint. We need to move to a more advanced sql program. These users already know ms sql, so naturally it's what we'd like to use.

 

[Eric Lehti]

the IBM POWER i system and JDE world connect easily to other architectures and systems in this heterogenous world. Several techniques are commercially available that enable you to join IBM data to MS SQL or other databases on other servers. Please let us know which one you select to use.
eric

 

[mrfields]

Hello Eric,
The 2005 edition of "Microsoft SQL Server Management Studio Express" is installed on my pc. I'd like to establish an ODBC from MSSQL to our jde data on our iseries. This will allow us to write and execute queries. Making that connection is a bit outside of my abilities as an accountant. Our IT staff tried some too, but without success. Everyone I've spoken to prior to posting this topic says it's a difficult thing to connect.
Can you help me get connected?
Thanks!

 

[millsmik]

Lookup how to create a linked server. I've had an issue trying to update data using a linked server but will be fine for inquiries.

 

[mrfields]

Hi millsmik,
Can that be done through ""Microsoft SQL Server Management Studio Express?"
Thanks

 

[millsmik]

Not sure about Express; try asking your IT folks or your SQL DBA.

 

[Krusador]

You need to use the Client Access ODBC Driver that comes with IBM Access – the emulator software that allows you to connect to the JDE machine. Create a new data source using that driver. On the Server tab of the driver setup window, specify your JDE library names. And on the Conversions tab make sure the ‘Convert binary data’ checkbox is checked.

Once that driver is setup, you should be able to select it from within MS SQL as a data source and run your queries.

All this being said, giving end-users access to run SQL statements against production data is just asking for trouble. I would strongly suggest that your IT department look into locking it down to just inquiry only. A quick solution may be to just create a separate library for the data for the queries. This data could be built on a nightly basis and would prevent having an accidental global update or delete of production data by an end user. That could be disastrous.

 

[mrfields]

Hi Bob, I think that's enough info to get us into the right direction. As for security, all info I've seen says update, insert, and drop functions won't work with this setup. We'll be very careful and/or lock down to inquiry only.
Thanks!

 

[MiniCNC]

Have you tried Squirrel? It's a freeware which does the same job on iSeries as MS Studio on ms db.

 

[mrfields]

Hi Bob, I'm here to revisit this topic as I've yet to get this working.
I created a new data source the way you mentioned, but when I open up ms sql 2005, I cannot locate any server. Do you have any other tips?
Thanks

 

[igor_g]

In MS SQL Server Management Studio, expand Server Objects then right click Linked Servers. In the context menu select New Linked Server... Then select MS OLE DB Provider for ODBC drivers and define your linked server.

Don't forget to setup security (you can simply map all local logins to a single remote one).


A tip: avoid direct inquiries to linked servers but use Microsoft's OPENQUERY instead.

Regards,