Solved

Oracle Database access from AS/400

Posted on 2009-04-15
11
876 Views
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
0
Comment
Question by:mayankgangrade
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
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:

http://www.oracle.com/corporate/pricing/technology-price-list.pdf
0
 
LVL 1

Author Comment

by:mayankgangrade
Comment Utility
How can I do that with out the tool ?
0
 
LVL 40

Expert Comment

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

0
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
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:  

http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.html
http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/rzaha/jdbc.htm (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:

http://www.goanywheretoday.com/features/connectivity/database
http://www.attunity.com/attunity_connect_data_connectivity
http://www.querytool.com/

- Gary Patterson
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:mayankgangrade
Comment Utility
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 ?
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
0
 
LVL 1

Author Comment

by:mayankgangrade
Comment Utility
Thanks for reply.

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

Regards,
Mayank
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
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.
0
 
LVL 34

Accepted Solution

by:
Gary Patterson earned 500 total points
Comment Utility
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.  

http://www.oracle.com/technology/products/id_mgmt/coreid_acc/index.html
"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
0
 
LVL 1

Author Closing Comment

by:mayankgangrade
Comment Utility
Thanks a Lot !!!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Windows 7 does not have the best desktop search built in. This is something Windows 7 users have struggled with. You type something in, and your search results don’t always match what you are looking for, or it doesn’t actually work at all. There ar…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now