Code generation with DB/2 on z/OS

[Jeff Keene]

Hi,

 

We have a need to generate code bindings from DB/2 on z/OS.  So far we've been doing it by copying the schema from the z/OS database into a DB/2 LUW, then running the code generator against the LUW.  We've had to do this because the jooq is coded to use the SYSCAT catalog, present on LUW but not on z/OS.

 

I'm looking for alternatives to this process.  Came up with a few ideas, but I was wondering if anyone else has solved this problem or has some different recommendations?

 

1. Find a way within jooq to map the SYSCAT schema over to SYSIBM.  I've done this just fine for regular data tables, but the catalog schema remapping is not something I see how to do.
2. Automate the schema synchronization with a tool like liquibase, and keep generating from an LUW DB/2
3. Create views onto SYSIBM named SYSCAT, sufficient to meet the needs of the jooq code generator.  I don't know quite how many views that would be, but looking at o.j.u.d.syscat.Tables it seems to start at thirteen.

 

Any thoughts or guidance would be appreciated.

 

Thanks,

Jeff

[Lukas Eder]

Hi Jeff,

Unfortunately, right now, we don't formally support DB/2 for z/OS (or for iSeries), as we do not have access to actual database instances for integration testing, which is needed to provide warranties and support for such an integration.

Nonetheless, I'm sure we can help you get this integration to work. Among your listed options, I suspect that SYSCAT and SYSIBM aren't similar enough to be used as substitutes for one another. So I think that option 1) can be ruled out. Option 2) would certainly work, but it seems a bit tedious. Option 3) would be interesting in general. I think that, if you open source your own version of SYSCAT views based on SYSIBM queries, this would be rather useful for a lot of people out there. We could help you promote such a project, if you're interested.

There are more options:

4. You can use the org.jooq.util.jdbc.JDBCDatabase from jOOQ-meta, which loads schemas, tables, sequences, and primary keys via the JDBC DatabaseMetaData API.

5. You can use jOOQ's org.jooq.util.xml.XMLDatabase and generate your database schema based on our XML version of the SQL standard INFORMATION_SCHEMA (http://www.jooq.org/xsd/jooq-meta-3.5.4.xsd)

6. You can implement your own org.jooq.util.Database in jOOQ-meta, and supply the necessary information to the code generator.

We would love to help you integrate with z/OS - our favourite option would be #6. We could maintain such an implementation for you, and add official support for DB2 for z/OS to the jOOQ Enterprise Edition.

I'm curious to hear what option seems to be the most reasonable to you, and whether you're interested in working on such an intergration with us.

Looking forward to hearing from you again,

Lukas - from Data Geekery

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jeff Keene]

Hi Lukas,

 

Thanks for the prompt reply, and apologies for my delay, had some customer issues divert me for a bit. 

 

I liked option four best, the JDBCDatabase as a source for the codegen, with option six, a full DB2 z/OS implementation a close second. 

 

When I tried option four, however, it ran an exceptionally long time before I stopped it and figured out what was happening.  The input side of reading the catalog metadata, with MetaImpl, does not have any filtering applied, so it was trying to read our entire catalog of 20+ schema, many having over 180 tables each, before generating code.  Remote over a VPN, it wasn't a fast process.

 

I made some local only edits to put in a filter and it ran in four minutes with perfect output.

 

Could we put in a feature request to filter the catalog reading by a schema regular expression, much as the output side generation has?

 

Option six, a full DB2 z/OS database class, remains tempting, though we've had utterly no problems with generating from DB2 LUW and shipping to our customers the generated code for the runtime.  Multiple production deployments against DB2 z/OS, no troubles whatsoever, kudos to you for that!  I'll talk over the potential effort/reward balance with management and email you directly what we might look at on this option.

 

Thanks,

Jeff

[Lukas Eder]

Hi Jeff,

Thanks a lot for your feedback. I see how the JDBCDatabase might run too long. You're right, we haven't implemented filtering on catalog metadata inside of MetaImpl. At the time MetaImpl was implemented, we weren't quite sure about which JDBC drivers actually adhered to the JDBC specifications. Today, we know this mostly affects MySQL and MariaDB. I guess we can fix this within MetaImpl and apply the available filtering:

https://github.com/jOOQ/jOOQ/issues/4235

Great to know that your DB2 LUW generation and z/OS shipping worked fine!

Best Regards,

Lukas

[Lukas Eder]

I've done some additional research about this issue and I've essentially discovered two types of meta queries that are slow within JDBCDatabase:

1) The query for primary keys: https://github.com/jOOQ/jOOQ/issues/4235

2) The listing of all schemas: https://github.com/jOOQ/jOOQ/issues/4240

Both of these issues result in querying all of the schemas / catalogs in the database, regardless of the <inputSchema/> setting for the code generator.

Both issues could be fixed easily and will be included in the jOOQ 3.6.1 patch release, that is due for today or tomorrow.

Best Regards,
Lukas