Oracle SQL Developer to iSeries - JDBC Services?


Legendary Poster

Trying to figure out what the best way to get iSeries DB2 Data into JDE on Windows using Oracle DB (Yup, I think I wrote that write).

On my Developer Box:
- I can connect to the Oracle DB on the Windows Box - that is good
- I've imported the .Jar files, that 'should' allow Oracle SQL Developer to talk to iSeries
* I can see the "DB2" Tab in SQL Developer - and I am putting in all the 'correct' values in the right boxes (at least I think I am)
- When I try to connect to the DB2 Database - I get, "Status : Failure -Test failed: [jcc][t4][2043][11550][3.57.82] Exception Error opening socket to server SiSeriesName/ on port 50,000 with message: Connection refused: connect. ERRORCODE=-4499, SQLSTATE=08001"

I'm pretty sure the client is setup - I'm not so sure that a JDBC Service is running, or I have the JDBC Service Port - on the iSeries.

Anyone wanna through some links or pointers? What am I missing? #LifeShouldBeSimpler?

- from E1 9.0, tools, DB2 on iSeries
- To E1 9.2, Tools 9.2.x.x, Oracle on Windows

Oracle SQL Developer should allow us to SQL data from the iSeries - just not sure what to turn on 'on the iSeries'....

um - why are you doing this ?

You should install Client Access onto your fat client, then create a datasource so you can see the data in the iSeries. If you want to move data from iSeries to Oracle, then use CopyTable. If you want to perform an upgrade, then you COPYTABLE the data in 9.0 format from iSeries to Oracle, and then run the conversions on the Oracle datasource to 9.2.

Or perhaps theres another reason ?

We are doing a 'Re-Implementation' - and not bringing all data across.

An option might be to bring the tables across to a "Conversion" environment, in their entirety - but our functional folk would like that to be a 'Converted' Environment (so we can flash that data to any environment). Gee, maybe a Conversion Env and a Converted Env?...

We are doing a lot of clean-up, and my plan is to script the changes - not just copy the whole tables.... Thus we are looking for an SQL-Type solution, where we can SQL-Script our conversion (against XRef tables)....

AND, with all that noise being said - what do I need to 'turn on' on the iSeries, to make the JDBC connector functional?


I don't believe you can connect SQL Developer to DB2/400 - it allows connectivity to DB2 UDB, but DB2/400 is completely different :

SQL Developer 2.1.1 and 3.0 support connections to IBM DB2 LUW 7, 8, 9 .
IBM DB2 iSeries and ZSeries Z/OS are not supported at this time.

Although - connecting jDeveloper may be possible :

However, you should definitely consider using a Conversion environment - and bringing everything across in its entirety. Using a JDE Fat Client and Table Conversions (in the way I've demonstrated many times previously) is the fastest method - you'd bring your 9.0 data from the iSeries into Oracle "as is" - ie, it will correctly convert "nulls" and other data dictionary items, ready for the cleanup and scripts. Running the Table Conversions in Parallel will ensure that all the data is very QUICKLY moved across. Depending on how large your database is, and how large your largest table is. But a very fast connection on a powerful PC will likely ensure that the data is moved across in a few hours, and can be repeatedly ran throughout your reimplementation.
Last edited by a moderator:
What's being shared appears to be 'what needs to be done on the "client" side'.

What/where do I need to install/configure/break-apart/set-fire-to - on the iSeries side (if anything)?

[h=2]Description[/h]The IBM Toolbox for Java / JTOpen is a library of Java classes supporting the client/server and internet programming models to a system running OS/400, i5/OS, or IBM i.

JTOpenLite is a set of lightweight Java classes suitable for use on mobile devices. It provides Java application access to IBM i: DDM access, basic JDBC access, command call, program call access, etc.

Packages are delivered by following PTF on IBM i platform:
V7R3: SI61258
V7R2: SI61255
V7R1: SI61254
AND what has to be turned on inside the iSeries OS? If JDBC is on by default - where do I confirm which port?

Using "Squirrel SQL" to test a JDBC Connection to our iSeries - I can confirm that you it is possible to connect an SQL Client to an iSeries via JDBC.

I can also confirm that it appears Oracle SQL Developer is not formatting the connect string correctly (OSD is BROKEN).....