Oracle Database access from AS/400

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
Who is Participating?
Gary PattersonConnect With a Mentor VP Technology / Senior Consultant Commented:
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
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:
mayankgangradeAuthor Commented:
How can I do that with out the tool ?
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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

Gary PattersonVP Technology / Senior Consultant Commented:
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
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.
mayankgangradeAuthor Commented:
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 ?
mayankgangradeAuthor Commented:
Thanks for reply.

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

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.
mayankgangradeAuthor Commented:
Thanks a Lot !!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.