Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Oracle Database access from AS/400

Posted on 2009-04-15
Medium Priority
Last Modified: 2013-12-06
Hi all,

I am working on a new project at the moment which involves Oracle and IBM AS/400 legacy system. I have been reading up on Oracle Access Manager and it seems to give AS/400 applications access to Oracle data.

Has anybody done this type of work before and if so any information would be very helpful.

Also does anybody know how much roughly the Oracle Access Manager costs?

Any advice information would help greatly. Thank you in advance for any information you can give
Question by:mayankgangrade
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
LVL 48

Expert Comment

ID: 24147299
Oracle Access Manager  is used for centralized identity management and access control.

I thing you can access the database without this tool.

The price list is here:

Author Comment

ID: 24147374
How can I do that with out the tool ?
LVL 40

Expert Comment

ID: 24148678
There is Java for OS/400, so you can use Oracle's thin client (ojdbc14.jar) to access via JDBC.

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

LVL 35

Expert Comment

by:Gary Patterson
ID: 24149315
Depending on your Oracle and DB2/400 versions, Oracle offers the following products:

Oracle Access Manager for AS/400 (not to be confused with Oracle Access Manager - the integrated identity tool) lets AS/400 RPG, COBOL, and C applications access Oracle data transparently - just like it is DB2/400 data.
Oracle Transparent Gateway for DB2/400 lets Oracle users access AS/400 data - to Oracle users the AS/400 database just looks like part of the Oracle database:
Oracle Database Gateway for DRDA allows Oracle to access DRDA-compliant databases (on z/OS, OS/390, DB2/400, and DB2 UDB).:  
Oracle Global Price List (if it ins't listed, you'll probably need to contact Oracle or a reseller for a quote, or it may be an obsolete product):  
Personally, before I spend 46K on licensing fees (like for the DRDA Gateway)I'd certainly explore using JDBC: (You didn't list and OS versions, so I just picked one.  Google VxRx JDBC where VxRy is your OS/400 version and release).

You can write, compile, and run your application in Java on the Oracle host, a seperate server running the OS of your choice, or on the AS/400, and using JDBC, you can easily access data stored in either DBMS.  JDBC is free on the AS/400, and it looks like the Oracle JDBC drivers are available for free download from the ORacle web site, too.  

The Oracle Thin ODBC driver is 100% pure Java, so it can run on any Java system - ORacle suggests using this driver unless your application specifically needs to use a different one.

There are also plenty of third party products, varying from simple batch import/export tools to gateways and connectors:

- Gary Patterson
LVL 48

Expert Comment

ID: 24151442
What is legacy?
AS 400 or the Information Systems on it?
For legacy systems Oracle provides precompilers.
Also SOA can leverage the interaction between such systems.

If you will build a new system on AS/400 I am almost sure Oracle will run and you will be able to connect and use Oracle. There are precompilers for Cobol for C for Fortran. Java is too advanced for batch processing what as I guess is your task.

Author Comment

ID: 24154756
I wanted to use RPG on AS400 to access the oracle database which remotely place on a different server ona different platform.
I am bit confused about the words used as JDBC and ODBC
Please clarify, what is the differenct between them ?

To conclude as disscussed above I have 3 options to go with (Please correct if I am wrong).

1) Use Oracle Access Manager for As400
what is the difference b/w "Oracle Access Manager for As400" and "Oracle Access Manager" ?
This inculdes cost of this package.

2) To use JDBC (which as said above is free),
Please explain this point in detail.
For this do I need to code in Java on AS400 ?

3) To use the other third party tools like attunity etc.
How do I use these tools and write queries in SQL in RPG program ?
LVL 48

Expert Comment

ID: 24155081

Author Comment

ID: 24155662
Thanks for reply.

Request you to resolve the queries in "04/15/09 10:48 PM, ID: 24154756" as well.

LVL 48

Expert Comment

ID: 24158751
I have a kind remark. This question should be posted on Oracle path of Experts-Exchange.
I do not take part on the AS 400 path

To access an Oracle server on remote host you have to:
1. Check if there are firewalls and obstacles between application and server machines

2. Figure out:

Host: the computer name or IP where Oracle works
Port; 1521 - the port where Oracle Listener waits for your connection
SID: the name of Oracle instance(there can be many instances)
Protocol: TCP

These parameter should be coded (see the examples in the docs) in the connection string.
The connection will be done using TCP/IP protocol.
LVL 35

Accepted Solution

Gary Patterson earned 2000 total points
ID: 24159351
For database access to Oracle from AS/400 RPG programs, Oracle's solution is Access Manager for AS/400.  You will probably need to contact Oracle for pricing, since it is not published in the current Global Price List.  You may also need to check and see if it is even available for your version of Oracle.

ODBC is an open standard and set of API's designed to allow access to many different kinds of databases through one probramming interface.  ODBC was originally created by Microsoft, and then later turned over as an open standard.  Most database vendors provide ODBC drivers for Windows (and other platforms) to allow Windows programs to access the vendor's database in a standard way.  For example, if you are a VB (or C#, or whatever) programmer, you can write an application using ODBC that will allow you to store and access data on any ODBC-compliant database, with no code changes required.  All that you would need to do the change DB platforms would be to install the correct ODBC driver, and configure an ODBC data source pointing to the correct database system.

JDBC is the Java equivalent of ODBC - Java Database Connectivity.  Read the Oracle Java FAQ above for much more information on JDBC, particularly in Oracle.  IBM also provides free JDBC drivers for DB2/400.

"...what is the difference b/w "Oracle Access Manager for AS400" and "Oracle Access Manager" ?"

As I said earlier, these are two unrelated products that just have unfortunately-similar names.  I already provided a link to the AM for AS/400 product in my original response above.  Oracle Access Manager is an identity management product that has nothing to do with accessing the AS/400 database.  For the purposes of this question, you can forget about it.  It only came up in the first place because the two products have confusing names.
"2) To use JDBC (which as said above is free),
Please explain this point in detail.
For this do I need to code in Java on AS400 ?"

Please read the Oracle JDBC FAQ (see link in my earlier post).  It really will explain a great deal about JDBC.  
As I said in my earlier post, you code in Java to use JDBC.  You cancode in Java on the ORacle server, you can code in Java on ther AS/400, or you can code in Java on a completely seperate server someplace else in your network.  JDBC allows your Java programs to attach to one, two, or more databases from anywhere.  Your program really doesn't need to know if it is talking to Oracle, SQL Server, MySQL, DB2/400, Access - it just talks to JDBC, and the JDBC layer handles the details of communicating with the underlying database.  The only time you need to know ehat database you are using is if you need to use DB-specific SQL syntax.  

For your project it might be most convenient to code the Java on the AS/400, especially if you want to access Oracle from RPG programs, since RPG programs can call Java programs and use Java objects.  If you want more info about accessing Java from RPG, I'd be happy to help, but please post a new question, since this one is getting big.
"3) To use the other third party tools like attunity etc.
How do I use these tools and write queries in SQL in RPG program ?"

You will need to refer to the product documentation for these various tools.  They all operate differently.  With some you would use the tool to copy data from remote files to local files, and then work on the local files.  Some may provide utilities or APIs that would allow you to directly access remote data from inside your RPG programs.
<!-- richText -->
- Gary Patterson

Author Closing Comment

ID: 31570375
Thanks a Lot !!!

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows 10 is here and for most admins this means frustration and challenges getting that first working Windows 10 image. As in my previous sysprep articles, I've put together a simple help guide to get you through this process. The aim is to achiev…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question