accessing Informatica File Archive Service using its native JDBC driver

exapat

Good morning,

Using the Informatica ILM suite, we've retired a number of database applications (which ran on Oracle) into the so-called Optimized File Archive, or OFA.
The information stored in there can be accessed using their File Archive Service, or FAS.
To allow access for certain tools, they've also got an ODBC and JDBC driver (from the original company that created the archive bit, RainStor), to be able to get to tables in archives.
I've been able to set it up for Aquafold's Aqua Data Studio (ADS), but given that that is shareware I've only got an evaluation version, expiring in 2 weeks.

Since SQLdeveloper, like ADS, is a Java-based tool, and it also allows for third party JDBC drivers, I'm inclined to think that somehow SQLdeveloper should also be able to connect to those archives. Unfortunately, I always get an error message: "Invalid connection information specified. Verify the URL format for the specified driver"

There are some slight differences between ADS and SQLdev in setting up such a JDBC connection.
In ADS, apart from providing user/pw, I have to specify following:
- name of the JDBC driver ("com.simba.client.core.SimbaJDBCDriver")
- location of the JDBC driver (e.g. "C:\Ora\RainStorJDBC-3.0.1.2.jar")
- JDBC URL of format "jdbc:simba://<host>:<port>;Archive=<archive>;ConnectionTimeout=3600;Parser=ORACLE"
In SQLdev I can import the JDBC driver (menu > Tools > Preferences, then under Database > Third Party JDBC Drivers), and in the Database Connection Screen create a new connection, provide the user/pw of the so-called Service Manager (from FAS), and choose Connection Type "Advanced" to then specify the "Custom JDBC URL", which would have to be the same as above. If I test the connection, I get an instant error:
"Status: Failure -Test failed: Invalid connection information specified. Verify the URL format for the specified driver".

Informatica's Global Customer Support are saying that SQLdev can't do it, but I'm just wondering whether some really bright lights in the Oracle community have found an alternative route to be able to connect to such FAS/OFA archives. Given the similarities between ADS and SQLdev, to me it seems it - somehow - has to be possible as well...

Thanks in advance for thinking about this problem/challenge.

Cheers!

Edited by: exapat on Dec 7, 2012 10:43 AM

Did some further investigations, on other third party drivers. Those ones (e.g. for SQL Server) - when loaded - create an extra tab in the connection screen.
The RainStor JDBC driver does not do that, and I can imagine that that's where it falls short.
If indeed the case, what could be done to overcome this?

Gary Graham-Oracle

Hi,

Did some further investigations, on other third party drivers ... If indeed the case, what could be done to overcome this?

That is the case. Currently browsing and migration support for third-party databases is limited to the following:
http://www.oracle.com/technetwork/developer-tools/sql-developer/supportedmigplatforms-086703.html

See section 1.1 for currently supported databases and section 1.3 for those planned for a future release.

To see an example of some (all?) of the extra bits needed to support browsing of a third-party database, find the extensions directory in your SQL Developer installation, look for

oracle.sqldeveloper.thirdparty.browsers.jar

then browse it to see the various class, properties, and xml files necessary for supporting connections to databases like mysql or sybase. You may conclude adding browsing support is not all that complicated. Migration support is an entirely different matter, however. Oracle is the thirdparty.browsers extension owner. Support for more third-party databases would normally be added by the SQL Developer group based on a database's general popularity or a feature request on the SQL Developer Exchange -- obviously a long-term proposition.

If you look at the [Extensions Exchange|http://www.oracle.com/technetwork/developer-tools/sql-developer/extensions-083825.html] maybe you can get some ideas how best to proceed if you have the resources to build your own extension to support RainStor.

Regards,
Gary
SQL Developer Team

exapat

Hi Gary,

Thanks for the swift reply, I'll have a look at the provided link/info shortly.

No need for migration support, read-only is perfect for this, since it's an archive scenario, i.e. the intent of the target 'database' is read-only anyway.

Cheers, Patrick

exapat

HI Gary,

Found the oracle.sqldeveloper.thirdparty.browsers.jar file in <SQLdeveloper home>\sqldeveloper\extensions but how do I browse it?
Opening it with Notepad++ shows me code, nothing legible though.
So I guess there's some tool to do this? If so, which one?

Thanks, Patrick

Gary Graham-Oracle

Hi Patrick,

A jar is just a zipped archive. I use a rather out-dated version of 7-Zip and it works fine.

-Gary

Edited by: Gary Graham on Dec 6, 2012 5:43 PM
You may also wish to search the web for a Java decompiler in order to read the class files.

exapat

Ah yes, using WinZip I can see what's in there.
Looking at the JDBC one, there are an images and xml folder, and a number of class files.
Having a look in the first .class file, using Notepad++ it's quite challenging to make out something useful.
And it's so full of control characters, that I can't even paste the content into this screen.

Apart from above challenge, I am not a Java person, and have only a basic understanding of XML.
Classes make little (or rather: no) sense to me, so I have no idea what to look for (once I get to reading those files).
What would you suggest for a Java/XML noob? (if there is anything to suggest that is...)

Ta, Patrick

Edited by: exapat on Dec 7, 2012 1:08 PM
your update must have arrived when I was in the process of drafting above response, I now see you added something about reading class files

Gary Graham-Oracle

I did not mean to imply that getting an unsupported JDBC driver to work with SQL Developer's browser would be easy, but only that it seemed doable given an appropriate level of Java development skills and the well-defined integration pattern we can see established for the other third-party JDBC drivers in the aforementioned jar file.

It would not surprise me if paying for an upgrade to Aquafold ADS's evaluation license proves to be your most cost-effective option.

-Gary

exapat

Hi Gary,

I think you're right there. It would only be 'cost-effective' if then hundreds of people start using SQLdeveloper to access the archives.
A scenario which will never arise, given the nature of those retired applications into the archive.
It's certainly a "very nice to have", just not a "must-have".

But maybe there are more people in the Oracle community who have a similar 'need', so who knows what might happen in the future...

Cheers, Patrick