The topic of this month's post is how to use a little known, but extremely useful, feature of Optim. That is the ability to create views using the Open Data Manager (which uses the Optim Connect infrastructure, which until recently was named Attunity Integration Server). I decided to write this post after an Information Insights customer asked me if there was a way to manipulate data coming in from one of Optim LUW's extended data sources, VSAM. You can use this same concept for that use case as well.
Normally when you try to create a view for an Optim Connect data source, it will tell you that the data source type does not support "CREATE VIEW". What it is really telling you is that it cannot run the CREATE VIEW statement on the underlying data source. You can create virtual views in the Optim Connect Middleware layer by creating a special data source called a virtual data source.
Once you have a virtual data source, you can switch to the metadata explorer and add views to it. The Virtual Data Source is aware of all the other data sources and data source shortcuts located on the Optim Connect Server. You can reference those external data sources by adding a prefix to the table names in your SQL statements. So, to select all the records in the OPTIM_CUSTOMERS table located in the ARCHIVE data source, you would connect to your virtual data source and execute:
SELECT * FROM ARCHIVE:OPTIM_CUSTOMERS
Now, the same concept applies for views, except you wrap your SQL statement inside a CREATE VIEW statement. You can either create the views inside Optim Connect Studio, or by connecting to the data source with the command line utility, nav_util and executing the CREATE VIEW statement that way.
There is also space to create synonyms and even stored procedures. That's all there is to it. Any user or application that wants to take advantage of the new views on the data can do so by connecting to the virtual data source instead of the original data sources. It adds some flexibility to Optim Connect, and can help prevent you from needing to add an additional layer of middleware (like InfoSphere Federation Server).