Solved

Oracle Database access from AS/400

Posted on 2009-04-15
11
961 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
[X]
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
11 Comments
 
LVL 48

Expert Comment

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

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

Author Comment

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

Expert Comment

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

0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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:  

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 48

Expert Comment

by:schwertner
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.
0
 
LVL 1

Author Comment

by:mayankgangrade
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 ?
0
 
LVL 48

Expert Comment

by:schwertner
ID: 24155081
0
 
LVL 1

Author Comment

by:mayankgangrade
ID: 24155662
Thanks for reply.

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

Regards,
Mayank
0
 
LVL 48

Expert Comment

by:schwertner
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.
0
 
LVL 35

Accepted Solution

by:
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.  

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
ID: 31570375
Thanks a Lot !!!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

I. Introduction There's an interesting discussion going on now in an Experts Exchange Group — Attachments with no extension (http://www.experts-exchange.com/discussions/210281/Attachments-with-no-extension.html). This reminded me of questions tha…
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 information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

624 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