# IBM AS400 Database connection **URL:** \ **Category:** Ignition\ **Created:** [March 10, 2010, 2:38pm UTC](https://forum.inductiveautomation.com/t/ibm-as400-database-connection/1619 "2010-03-10T14:38:46Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![jim\_lapratt](https://avatars.discourse-cdn.com/v4/letter/j/cc9497/32.png) [@jim\_lapratt](https://forum.inductiveautomation.com/u/jim_lapratt)\ **Post date:** [March 10, 2010, 2:38pm UTC](https://forum.inductiveautomation.com/t/ibm-as400-database-connection/1619/1 "2010-03-10T14:38:46Z")
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? ---
**Author:** ![bryan\_cook](https://avatars.discourse-cdn.com/v4/letter/b/a8b319/32.png) [@bryan\_cook](https://forum.inductiveautomation.com/u/bryan_cook)\ **Post date:** [March 10, 2010, 3:10pm UTC](https://forum.inductiveautomation.com/t/ibm-as400-database-connection/1619/2 "2010-03-10T15:10:46Z")
Total stab in the dark here, but maybe you could use [JTOpen](http://jt400.sourceforge.net/)? … 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 🙂 ---
**Author:** ![jim\_lapratt](https://avatars.discourse-cdn.com/v4/letter/j/cc9497/32.png) [@jim\_lapratt](https://forum.inductiveautomation.com/u/jim_lapratt)\ **Post date:** [March 10, 2010, 5:43pm UTC](https://forum.inductiveautomation.com/t/ibm-as400-database-connection/1619/3 "2010-03-10T17:43:59Z")
I was looking at that also. Looks like it should work, now to get it setup. ---
**Author:** ![jim\_lapratt](https://avatars.discourse-cdn.com/v4/letter/j/cc9497/32.png) [@jim\_lapratt](https://forum.inductiveautomation.com/u/jim_lapratt)\ **Post date:** [March 10, 2010, 5:45pm UTC](https://forum.inductiveautomation.com/t/ibm-as400-database-connection/1619/4 "2010-03-10T17:45:53Z")
Can this be set to read/query only? ---
**Author:** ![bryan\_cook](https://avatars.discourse-cdn.com/v4/letter/b/a8b319/32.png) [@bryan\_cook](https://forum.inductiveautomation.com/u/bryan_cook)\ **Post date:** [March 10, 2010, 6:05pm UTC](https://forum.inductiveautomation.com/t/ibm-as400-database-connection/1619/5 "2010-03-10T18:05:51Z")
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? ---
**Author:** ![jim\_lapratt](https://avatars.discourse-cdn.com/v4/letter/j/cc9497/32.png) [@jim\_lapratt](https://forum.inductiveautomation.com/u/jim_lapratt)\ **Post date:** [March 10, 2010, 7:33pm UTC](https://forum.inductiveautomation.com/t/ibm-as400-database-connection/1619/6 "2010-03-10T19:33:30Z")
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. ---
**Author:** ![bryan\_cook](https://avatars.discourse-cdn.com/v4/letter/b/a8b319/32.png) [@bryan\_cook](https://forum.inductiveautomation.com/u/bryan_cook)\ **Post date:** [March 10, 2010, 7:42pm UTC](https://forum.inductiveautomation.com/t/ibm-as400-database-connection/1619/7 "2010-03-10T19:42:20Z")
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 🙂 Have you shot an e-mail over to [support](http://www.inductiveautomation.com/support) yet? Regardless, I’m sure someone will respond soon enough … Good luck! ---
**Author:** ![jim\_lapratt](https://avatars.discourse-cdn.com/v4/letter/j/cc9497/32.png) [@jim\_lapratt](https://forum.inductiveautomation.com/u/jim_lapratt)\ **Post date:** [March 10, 2010, 7:50pm UTC](https://forum.inductiveautomation.com/t/ibm-as400-database-connection/1619/8 "2010-03-10T19:50:09Z")
Thanks Bryon, For the most part I have copied the INFO within my ODBC connection on my windows box into this, still no luck. ---
**Author:** ![jim\_lapratt](https://avatars.discourse-cdn.com/v4/letter/j/cc9497/32.png) [@jim\_lapratt](https://forum.inductiveautomation.com/u/jim_lapratt)\ **Post date:** [March 10, 2010, 8:00pm UTC](https://forum.inductiveautomation.com/t/ibm-as400-database-connection/1619/9 "2010-03-10T20:00:52Z")
I am wondering if Ignition takes care of adding it to the class path, or if I need to reboot to make it fly. ---
**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\ **Post date:** [March 11, 2010, 4:45pm UTC](https://forum.inductiveautomation.com/t/ibm-as400-database-connection/1619/10 "2010-03-11T16:45:55Z")
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. ---
**Author:** ![jim\_lapratt](https://avatars.discourse-cdn.com/v4/letter/j/cc9497/32.png) [@jim\_lapratt](https://forum.inductiveautomation.com/u/jim_lapratt)\ **Post date:** [March 12, 2010, 6:37pm UTC](https://forum.inductiveautomation.com/t/ibm-as400-database-connection/1619/11 "2010-03-12T18:37:30Z")
Here are 3 screen shots. The one from windows is of the working odbc connection on my workstation. ![](https://us1.discourse-cdn.com/inductiveautomation/original/1X/f502d7a92b33127d66484d9e3e96b4170b4a4abe.JPG) ![](https://us1.discourse-cdn.com/inductiveautomation/original/1X/6a1389d9e3bbc38d6f7d77cc08d792a505ab0fc0.JPG) ![](https://us1.discourse-cdn.com/inductiveautomation/original/1X/622778ae0d602acdf64bc9b80eef3dc111a2e2ac.JPG) ---
**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\ **Post date:** [March 13, 2010, 12:49am UTC](https://forum.inductiveautomation.com/t/ibm-as400-database-connection/1619/12 "2010-03-13T00:49:04Z")
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. ---
**Author:** ![jim\_lapratt](https://avatars.discourse-cdn.com/v4/letter/j/cc9497/32.png) [@jim\_lapratt](https://forum.inductiveautomation.com/u/jim_lapratt)\ **Post date:** [March 15, 2010, 6:16pm UTC](https://forum.inductiveautomation.com/t/ibm-as400-database-connection/1619/13 "2010-03-15T18:16:38Z")
Carl, Any ideas what I should put in there??? ---
**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\ **Post date:** [March 15, 2010, 7:12pm UTC](https://forum.inductiveautomation.com/t/ibm-as400-database-connection/1619/14 "2010-03-15T19:12:38Z")
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. ---
**Author:** ![bryan\_cook](https://avatars.discourse-cdn.com/v4/letter/b/a8b319/32.png) [@bryan\_cook](https://forum.inductiveautomation.com/u/bryan_cook)\ **Post date:** [March 16, 2010, 1:13pm UTC](https://forum.inductiveautomation.com/t/ibm-as400-database-connection/1619/15 "2010-03-16T13:13:21Z")
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 … ---
**Author:** ![bryan\_cook](https://avatars.discourse-cdn.com/v4/letter/b/a8b319/32.png) [@bryan\_cook](https://forum.inductiveautomation.com/u/bryan_cook)\ **Post date:** [March 16, 2010, 1:46pm UTC](https://forum.inductiveautomation.com/t/ibm-as400-database-connection/1619/16 "2010-03-16T13:46:55Z")
Assuming it’s DB2 running on the machine, try … ```auto select * from sysibm.sysdummy1 ``` … which should return a single column IBMREQD with a value of Y … ---
**Author:** ![jim\_lapratt](https://avatars.discourse-cdn.com/v4/letter/j/cc9497/32.png) [@jim\_lapratt](https://forum.inductiveautomation.com/u/jim_lapratt)\ **Post date:** [March 17, 2010, 1:48pm UTC](https://forum.inductiveautomation.com/t/ibm-as400-database-connection/1619/17 "2010-03-17T13:48:46Z")
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. ---
**Author:** ![jim\_lapratt](https://avatars.discourse-cdn.com/v4/letter/j/cc9497/32.png) [@jim\_lapratt](https://forum.inductiveautomation.com/u/jim_lapratt)\ **Post date:** [March 17, 2010, 1:51pm UTC](https://forum.inductiveautomation.com/t/ibm-as400-database-connection/1619/18 "2010-03-17T13:51:57Z")
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. ---
**Author:** ![jim\_lapratt](https://avatars.discourse-cdn.com/v4/letter/j/cc9497/32.png) [@jim\_lapratt](https://forum.inductiveautomation.com/u/jim_lapratt)\ **Post date:** [March 17, 2010, 1:58pm UTC](https://forum.inductiveautomation.com/t/ibm-as400-database-connection/1619/19 "2010-03-17T13:58:46Z")
Got it Add: libraries=hcidata to the “Extra Connection Properties” Thanks all ---
**Author:** ![bryan\_cook](https://avatars.discourse-cdn.com/v4/letter/b/a8b319/32.png) [@bryan\_cook](https://forum.inductiveautomation.com/u/bryan_cook)\ **Post date:** [March 17, 2010, 2:02pm UTC](https://forum.inductiveautomation.com/t/ibm-as400-database-connection/1619/20 "2010-03-17T14:02:51Z")
No problem, glad your making progress … the documentation found at [this link](http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmstintsel.htm#intsel) might help when writing up your queries … … and [I can’t take all the credit](http://stackoverflow.com/questions/2454699/as-400-select-1-equivalent) 🙂 [Next page](https://forum.inductiveautomation.com/t/ibm-as400-database-connection/1619.md?page=2)