As mentioned in my earlier blog, I work on heterogeneous data accessand I am one of the technical leads responsible for Oracle integration.Until we get more detail out on developerWorks, I want to provide youwith an overview of the support we will have for Oracle
Curt hinted in his announcement blog about Oracle support in the upcoming release of Optim products that were formerly named Data Studio. Support for Oracle has been been expanded in Optim Development Studio (formerly Data Studio Developer) to extend to the development environment the capabilities already available in InfoSphere Data Architect (for modeling and design) and the Optim solutions for data privacy, data growth, and test data management.
With Optim Development Studio 2.2, support for Oracle has been added in the areas of:
- Object Management - You can explore into your Oracle database using the Data Source Explorer and view/create/alter/drop tables, views, materialized views, sequences, synonyms, indexes and user-defined types. And as usual, for any of the edits that you make it generates appropriate DDL to be deployed to the Oracle backend. Furthermore, using the Data Source Explorer, you can view contents of your existing stored procedures, functions and PL/SQL packages.
- PL/SQL life cycle management - Start by creating a new PL/SQL package in a Data Development project (specification and body), add contents to it and then deploy the package to the server (with debug enabled), debug a PL/SQL package stored procedure or function. The debug works like the standard Eclipse Java debugger: you can set breakpoints where you want, and step through and look at changes to variables defined in your stored procedure or function. You can also copy a package from the Data Source Explorer to a Data Development project and edit the package / deploy it back or debug the package entities from there. The same use case scenario extends to PL/SQL based stored procedures and functions as well.
- Data Management - Look at/Export/Import/Edit the contents for a table or view. Copy schema objects from one Oracle schema to the other (enforce data privacy rules on the copy over if required - this applies to copying over a table with data from one schema to another). You can also use the new Copy functionality to keep your DB2 implementation current with your Oracle implementation by copying the impacted database objects between the two database servers. This can only be done with the new DB2 Linux, UNIX, Windows 9.7 release with the DB2 database in the Oracle compatibility mode.
- Visual Explain - View the Visual Explain of a particular SQL statement by right clicking on the SQL statement and then clicking on Open Visual Explain. This action can be performed from the SQL Editor, from the pureQuery-enabled Java editor and the SQL outline view. This shows an explain graph for the statement with details on the different nodes/operators (type of operator, cost / cardinality etc). Also, right clicking on a particular node in the graph will show additional details (for a table operator, you get to see the columns, indexes with related data).
pureQuery support has been added for Oracle with this release as well. You should be able to use the pureQuery scenarios as listed in this article except for the dynamic-to-static and the JPA ones. There are several enhancements to pureQuery-based development that have been introduced with this release. Sonali Surange will be publishing an article on this soon.
As Curt mentions in his blog, Oracle support in the product(s) adds to the heterogeneity of the product set and provides people who need to develop cross-database applications a single platform for developing applications with a consistent look and feel. And this development environment is part of a larger integrated data management suite that provides heterogeneous data lifecycle capabilities from design through application retirement.
Keep an eye on the Integrated Data Management Community Space, which should have a link to the updated trial download before long. The announcement letter also indicates the Oracle versions and drivers supported. Please try the product out and give us your valued feedback.
-- Venkatesh Gopal