IBM AS400 Database connection

Can someone point me to a document to setup ignition to read data from a AS400 database.

Which JDBC driver and how to install in Ubuntu?

Total stab in the dark here, but maybe you could use JTOpen?

… you’d have to add the driver within Ignition using the Configure | Database | Drivers page. I’d try download JTOpen, unzipping the files and using jt400.jar.

Again, just a wild guess but it might be worth an attempt while your waiting for the sun to rise on the West Coast :slight_smile:

I was looking at that also. Looks like it should work, now to get it setup.

Can this be set to read/query only?

I’m not sure how things operate on the AS400 side, but could you assign read-only permissions to the account you’re using?

I follow the above procedure when making connections to SQL databases from any application … configure an application-specific account with read-only privileges, (assuming read-only access is all I need) …

Are you having any luck with JTOpen?

Well can’t get it to connect. I have tried about everything I can find in the connection URL, any idea’s?
I have tried not port and have tried port 3470.
I am going to try the IP of the as400 next to see if it works.

I’m afraid I can’t help beyond what I recommended earlier … sorry … never programmed much with Java so I’m not familiar with JDBC connections/connection strings … additionally, when the AS400 made its debut I was 5 years old :slight_smile:

Have you shot an e-mail over to support yet? Regardless, I’m sure someone will respond soon enough …

Good luck!

Thanks Bryon,

For the most part I have copied the INFO within my ODBC connection on my windows box into this, still no luck.

I am wondering if Ignition takes care of adding it to the class path, or if I need to reboot to make it fly.

If you load the JDBC driver through the Ignition gateway, it will take care of the classpath on the fly.

Perhaps you could post more information about the connect URL you have configured it to use, and the specific fault error that the Gateway’s Status/Database Connections page is reporting.

Here are 3 screen shots. The one from windows is of the working odbc connection on my workstation.






It looks like it is connecting, but is failing to run the Validation Query. This is a query that Ignition will run to really make sure that the connection is good. It is any query that must return any non-empty result set.

You can set it, it is an advanced property of your connection. My guess it is defaulted to SELECT 1, which apparently AS400 does not like. This isn’t uncommon - for example Oracle doesn’t like SELECT 1 either - it needs “SELECT 1 FROM DUAL”. You’ll have to provide it a query that AS400 likes.

Carl,

Any ideas what I should put in there???

Any query that returns at least 1 row and doesn’t cause an error. I’m sorry, I’m not very familiar with AS/400.

I’ve got an e-mail out to a friend of a co-worker who maintains an AS/400 … I’ll relay the response as soon as I get it …

Assuming it’s DB2 running on the machine, try …

select * from sysibm.sysdummy1

… which should return a single column IBMREQD with a value of Y

1 Like

I got this much of it…

Select 1 from sysibm.sysdummy1

Will allow it to connect. Now I need it to go to the the “hcidata” that is listed in the screenshot above.

Thanks Bryan,

I have been beating my head against wall for last day now trying to get to the “hcidata” library. I have tried about every example I can find on net with now success.

Got it

Add:

libraries=hcidata

to the “Extra Connection Properties”

Thanks all

1 Like

No problem, glad your making progress … the documentation found at this link might help when writing up your queries …

… and I can’t take all the credit :slight_smile: