Oracle Database access from AS/400

Posted on 2009-04-15
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
  • 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.

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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 500 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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How do I call MySQL Stored Procedure from oracle using HS link ? 5 58
wireless name in LAN adapter 14 69
migration MS SQL database to Oracle 30 68
Email query results in HTML 6 29
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
In this article we will discuss all things related to StageFright bug, the most vulnerable bug of android devices.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

856 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