squirrel-sql-develop

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

[<den...@ne...>]

 I believe the post should better have gone to the developers list.

Sorry for that.
Denis.


 


 

-----Original Message-----
From: den...@ne...
To: squ...@li...
Sent: Fri, Aug 21, 2009 11:17 am
Subject: IMS mainframe hierarchic database SQL Driver works, but I have questions for enhancing Squirrel









Hi,



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=2
0to 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.

Denis Gäbler.


 



 

[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())