[Squirrel-sql-develop] Fwd: IMS mainframe hierarchic database SQL Driver works, but I have questions for enhancing Squirrel

[Robert M. <rob...@gm...>]

Hello,

I'll take a stab at answering your questions - see below.

Rob


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?

Generally, no, however you can turn debug logging on and many classes
log SQL being generated to debug.  Change "info" to "debug" in
log4j.properties:

log4j.rootLogger=DEBUG, SquirrelAppender

If you are handy with AOP, you could try applying advice to
Statement.executeQuery(...) and
PreparedStatement.prepareStatement(...).

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?

Yes, the dialect controls what is available to the refactoring plugin
with regard to DDL.

3. IMS DB does not support Autoincrement, this I have to tell Squirrel
through the SQL Dialect, correct?

The interface net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect
provides a method for this (supportsAutoIncrement) and many other
database features.  If your IMS dialect extends
CommonHibernateDialect, then no support for AutoIncrement is the
default behavior.

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.

See DataSetUpdateableTableModelImpl.  It defers a bit of SQL
generation to IDataTypeComponent implementations, but see
updateTableComponent which calls constructUpdateSql().  We would
probably need to introduce a new plugin API for intercepting and/or
providing this SQL.

5. IMS Datatypes through the SQL Dialect, correct?

Yes - for a minimal implementation that possibly doesn't fully support
refactoring, take a look at IntersystemsCacheDialectExt.

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?

Does the driver return a unique SQL Data Type code for these columns ?
If so, then I would say a custom IDatatypeComponent implementation in
the plugin would give you what you need.  If not then having the
plugin provide custom metadata may be the only good option (see
response to #7 below).

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?

If there is a great amount of metadata that cannot be retreived from
the driver (such as fks, pks, tables, columns, etc.), then perhaps we
need a new plugin API to allow you to intercept those calls that would
otherwise fail or return erroneous results.  What I'm thinking here is
to allow plugins to install an ISQLDatabaseMetaData factory that
SQLConnection could use when it instantiates it's SQLDatabaseMetaData.
 We have an inteface (ISQLDatabaseMetaData) that could be used, and
the plugin could create an custom implementation that extends
SQLDatabaseMetaData and overrides whichever metadata calls to the
driver would otherwise fail.  Session plugins get a callback
(sessionStarted) which could be used to install the factory.   I have
for a long time thought that this would be nice so that we can remove
database-specific tweaks from SQLDatabaseMetaData.  For now,  you can
simply modify SQLDatabaseMetaData locally to use reflection into your
Metadata class for prototyping purposes.

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?

You can specify additional database connection properties via "Alias
Properties".  If you can specify multiple database class names via
properties (for example : DriverManager.connect(user, pass,
properties) ), then this should be possible.

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)

This could probably also be addressed by custom ISQLDatabaseMetaData
factory implementation provide by the plugin to the session when the
session is started/created (see getTables())