the latest IMS DB (hierarchic Mainframe Database) also got an JDBC driver.
I managed to issue SQL Queries against the database with Squirrel, however there is room for improvement, e.g. Updating a field does not work, because the SQL being created cannot be executed by the driver. I would like to do some research and provide some code to make the IMS JDBC Driver work better with Squirrel, unfortunatly I cannot change the driver, the SQL it accepts and the behaviour. I assume that I have to create an IMSDialect on the hiberate API and also to write some enhancements to Squirrel and of course an IMS Plugin for Squirrel.
I downloaded the latest source and imported it into Eclipse, however it is not clear to me, where I have to put/implement certain things:
The segments in the hierarchy are treated as Tables, such as that if an update in the 3rd hierarchic level has to be made, the WHERE clause always has to reference the keys and table names from the above levels. So I have to create custom SQL e.g. for updating.
For the IMS Driver the segment names, hierarchic level, etc. are put into class file, which needs to be part of the classpath, IMS does not have a catalog - class is called DLIDatabaseView. Those java classes can be generated, but unfortunatly are part of the connection URL for the JDBC Driver.
So here are my questions:
1. Is there a setting in Squirrel, that can display the SQL that is sent to
the JDBC Driver, such as, that I can check if the SQL is correct?
2. Some SQL clauses are not supported by the IMS Driver, e.g. ALTER TABLE, etc. this I probably have to tell Squirrel through the SQL Dialect, correct?
3. IMS DB does not support Autoincrement, this I have to tell Squirrel through the SQL Dialect, correct?
4. Where do I have to tell Squirrel, that it should use a different SQL to do Updates through the IMS JDBC Driver? I noticed, that update did not work for a root segment and I wonder where Squirrel creates the SQL and if there is an interface which can be used to change the SQL for the IMS JDBC Driver? I really would like Squirrel to be able to update the data.
5. IMS Datatypes through the SQL Dialect, correct?
6. IMS has an XML feature, which can return a complete record, FLOWR subsets, complete IMS Database as XML Document, is that be implemented with a custom type in an IMS Plugin?
7. The foreign Keys between the tables can be obtained from the Metadata class file using reflection API, but not with SQL to a catalog or so. I saw in other plugins source, that a custom SQL can be returned, which would not be possible for the IMS Driver. How would I return such data to Squirrel by creating either a resultset or an array that contains this information about the relationship of the IMS Databases?
8. An IMS Databaseview.class only represents one Database, which can contain multiple Tables (IMS Segments). Now the Databaseview has to be part=2
0of the connection URL. If I would like to define multiple databases in Squirell, I need a separate Alias for every database. Is there a way to collect all Databaseview class names from the classpath (can be done, but how to tell Squirrel about it) and provide those as a variable to the connection string, thus being able to have multiple IMS Databases in a single Alias view?
9. Not all IMS Tables relations are discovered by the graph plugin, is there a way to tell the graph plugin about the IMS Table relations? (saw that can be done with returning SQL, however that is not possible with IMS, I have to fetch the information out of the DLIDatabaseView classes and return somehow)
There are probably more question.
Thanks in advance.