Connecting to Db2 for z/OS with ibm_db_sa and SQLAlchemy – reflector/schema selection issue

[rhgit01]

The current version of ibm_db_sa (v0.4.0) is running into problems when certain SQLAlchemy (SA) functions are used with Db2 for z/OS.

E.g., a call to SA Base.metadata.create_all(engine) triggers an exception: “ibm_db_dbi.Error: ibm_db_dbi::Error: Exception("{DB2 FOR OS/390}{ODBC DRIVER}{DSN12015} DSNT408I SQLCODE = -204, ERROR: SYSCAT.TABLES IS AN UNDEFINED NAME”.
The problem is that ibm_db_sa is looking for the catalog information in SYSCAT.TABLES. This is fine for some Db2 versions, but in Db2 for z/OS this table doesn’t exist, the correct catalog table would be SYSIBM.SYSTABLES.

Diving into the issue, I noticed that there is an OS390Reflector in ibm_db_sa, but it is not selected. In class DB2Dialect the initialize() method identifies the target DBMS (dbms_name) and selects the appropriate reflector. In case of Db2 for z/OS, the dbms_name returned from Db2 starts with "DSN" followed by version/release/modification level identifiers. Since initialize() currently doesn't handle "DSN", the standard DB2Reflector is being used, pointing to tables (e.g., SYSCAT.TABLES) that don't exist in Db2 for z/OS, leading to subsequent exceptions.

The minor change suggested in the subsequent PR extends the selection in initialize(). It assigns the existing OS390Reflector (instead of the DB2Reflector) for dbms_names starting with "DSN", thus enabling additional functions to work with Db2 for z/OS. The additional elif is introduced at the very end to minimize risks associated with this change.

Further details on the Db2 for z/OS naming schema can be found here:
https://www.ibm.com/docs/en/db2-for-zos/13?topic=work-product-identifier-prdid-values-in-db2-zos

Any feedback or suggestions to improve are greatly appreciated. Thanks!

[bchoudhary6415]

Hello @rhgit01
Closing this issue as PR #147 is merged.