Connecting to an IBM i midrange (AS400) with ibmdbsa via SQLAlchemy/Apache Superset

[jedatcrs]

Platform: Ubuntu 22.04 (x64) ibm_db_sa version: 0.4.0 ibm_db version: 3.1.4 Python version: 3.10 SQLAlchemy version: 1.4.48 pyODBC version: 4.0.39 Apache Superset version: 2.1.0 IBM i (AS400): V7R3M0 ibm-iaccess version: 1.1.0.27-1.0

Hello all. I'm trying to connect my newly installed Apache Superset to our AS400 using ibm_db_sa. Using the connect string recommended by Superset, I get this error: connect string: ibm_db_sa://username:password@ipaddress:446/machinename superset.exceptions.SupersetErrorsException: [SupersetError(message='(builtins.NoneType) None\n[SQL: (ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: [IBM][CLI Driver] SQL1598N An attempt to connect to the database server failed because of a licensing problem. SQLSTATE=42968 SQLCODE=-1598\n(Background on this error at: https://sqlalche.me/e/14/f405)]\n(Background on this error at: https://sqlalche.me/e/14/dbapi)', error_type=<SupersetErrorType.GENERIC_DB_ENGINE_ERROR: 'GENERIC_DB_ENGINE_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'engine_name': 'IBM Db2', 'issue_codes': [{'code': 1002, 'message': 'Issue 1002 - The database returned an unexpected error.'}]})]

This sorta makes sense, since we are not using DB2 Connect. Reading through the posts and snippets I've gleamed from here and other sites, I would be better off using pyODBC. This makes sense to me, since I know the ODBC stuff works perfectly with our IBM i. So I modified my connect string like so: connect string: ibm_db_sa+pyodbc://username:password@ipaddress/machinename and this is the error: superset.exceptions.SupersetErrorsException: [SupersetError(message='(builtins.NoneType) None\n[SQL: (pyodbc.Error) (\'01000\', "[01000] [unixODBC][Driver Manager]Can\'t open lib \'IBM DB2 ODBC DRIVER\' : file not found (0) (SQLDriverConnect)")\n(Background on this error at: https://sqlalche.me/e/14/dbapi)]\n(Background on this error at: https://sqlalche.me/e/14/dbapi)', error_type=<SupersetErrorType.GENERIC_DB_ENGINE_ERROR: 'GENERIC_DB_ENGINE_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'engine_name': 'IBM Db2', 'issue_codes': [{'code': 1002, 'message': 'Issue 1002 - The database returned an unexpected error.'}]})]

Interestingly, the "IBM DB2 ODBC DRIVER" is not the correct ODBC driver. It should be using "IBM i Access ODBC Driver". I verified that the ODBC drivers are working properly by creating an DSN in /etc/odbc.ini and opening it in "LibreOffice Base". It seems to work exactly as it should there.

Googling more, I ran across this article: https://stackoverflow.com/questions/35461388/connecting-to-ibm-as400-server-for-database-operations-hangs

The user "John Y" talked about an AS400 class in ibm_db_sa that isn't really used, and showed a simple mod to allow it to work. Doing his suggestion, I've gotten closer. I also had to modify the reflection.py file, and replace the unicode() function with str(). Further, he suggested the entire package be copied into the "dialects" directory in sqlalchemy.

With all that done, I am finally able to test and connect our IBM i using this connect string: ibm_db_sa+pyodbc://username:password@ipaddress/machinename

Going into the SQL Lab (in Apache Superset) though, after selecting a library on our IBM i, it never shows the files/tables available. Looking at the logs, it has this: Unable to load SQLAlchemy dialect <class 'ibm_db_sa.pyodbc.AS400Dialect_pyodbc'>: No module named 'sqlalchemy.connectors.zxJDBC' 2023-06-13 09:11:02,547:WARNING:superset.db_engine_specs:Unable to load SQLAlchemy dialect <class 'ibm_db_sa.pyodbc.AS400Dialect_pyodbc'>: No module named 'sqlalchemy.connectors.zxJDBC'

This seems to repeat for any schema/library I select. Googling that error, it seems that SQLAlchemy has dropped support for sqlalchemy.connectors.zxJDBC, and someone suggested rolling back to SQLAlchemy 1.3.X. Unfortunately, Superset requires 1.4 or greater.

Is there any hope for getting our IBM i connected? Any pointers or suggestions would be helpful. Thank you for reading.

[bchoudhary6415]

Hi @jedatcrs Thanks a lot for the detailed explanation. Let me have a look into the request a bit more and I will let you know.

You are talking about some changes in ibm_db_sa If possible can you please share what exact changes you made?

Let me have a more detailed look into this and come back.

Thanks.

[jedatcrs]

Thanks for taking a look at this issue. From that link, the user suggested appending the code below at the bottom of pyodbc.py

    def initialize(self, connection):
        super(DB2Dialect, self).initialize(connection)

dialect = AS400Dialect_pyodbc

His reasoning and explanation are in that link above. I also went in and modified reflection.py some, just to squash some errors in the log regarding the use of unicode(), instead of str().

[bchoudhary6415]

Hi @jedatcrs Thanks for sharing above details. Let me have a more detailed look into this and come back.

[jedatcrs]

Just an update, but I learned that my syntax for IBM i should probably be like so: connect string: ibm_db_sa+pyodbc400://username:password@ipaddress/machinename Doing so at least appears to connect using the correct ODBC drivers (It didn't before, since I omitted the "400" bit), but fails some SCHEMATA stuff. Possibly a dialect issue? sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42S02', '[42S02] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0204 - SCHEMATA in SYSCAT type *FILE not found. (-204) (SQLPrepare)') [SQL: SELECT "SYSCAT"."SCHEMATA"."SCHEMANAME" FROM "SYSCAT"."SCHEMATA" WHERE "SYSCAT"."SCHEMATA"."SCHEMANAME" NOT LIKE ? ORDER BY "SYSCAT"."SCHEMATA"."SCHEMANAME"] [parameters: ('SYS%',)] I've removed the modifications that were mentioned in my previous message, and am using stock ibm_db_sa 0.4.0

[rfx77]

the problem is that with pyodbc400 the attribute dbms_name in the initialize method of class DB2Dialect is always None and not AS. Therfore the AS400Reflector is never chosen.

When i hardcode it to AS everyting works fine.

    def initialize(self, connection):
        self.dbms_ver = getattr(connection.connection, 'dbms_ver', None)
        self.dbms_name = getattr(connection.connection, 'dbms_name', None)
        DB2Dialect.serverType = self.dbms_name
        super(DB2Dialect, self).initialize(connection)
        # check server type logic here
        _reflector_cls = ibm_reflection.DB2Reflector
        if self.dbms_name == 'AS':
            _reflector_cls = ibm_reflection.AS400Reflector
        elif self.dbms_name == "DB2":
            _reflector_cls = ibm_reflection.OS390Reflector

[bchoudhary6415]

Hello @rfx77 Can you please share where did you added the above code? and can you share sample repo to reproduce the issue and can be checked it works fine with above changes?

Thank you

[rfx77]

I did not add the code. It is the existing code where the error is located.

[jedatcrs]

@rfx77 Nice to see another IBM i user. So, I hardcoded dbms_name, like so:

    def initialize(self, connection):
        self.dbms_ver = getattr(connection.connection, 'dbms_ver', None)
        self.dbms_name = getattr(connection.connection, 'dbms_name', None)
        self.dbms_name = "AS"

After modifying the reflection.py file (I see you also opened a ticket about that), I can Test, and Connect in Superset, and under schema, I see the IBM i's libraries, however, the table schema isn't populating. It may be something I'm doing wrong. @rfx77 are there any other modifications you've made to ibm_db_sa to get it to work with your IBM i?

[rfx77]

https://github.com/ibmdb/python-ibmdbsa/issues/137#issue-1888383905

You have to fix the unicode problem.

try to add this to this line: https://github.com/ibmdb/python-ibmdbsa/blob/2a0fe785daa3cb8dc4d911fb1d3b13cd2a745aea/ibm_db_sa/reflection.py#L29

unicode = Unicode

then loading the tables should work. the remaining problem with my schema is that all foreign-keys are returned doubled. seems to be another bug.

[jedatcrs]

Oddly, unicode = Unicode gives me problems..

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('Invalid parameter type.  param-index=0 param-type=Unicode', 'HY105')
[SQL: SELECT "SYSIBM"."SQLSCHEMAS"."TABLE_SCHEM" 
FROM "SYSIBM"."SQLSCHEMAS" 
WHERE "SYSIBM"."SQLSCHEMAS"."TABLE_SCHEM" NOT LIKE ? AND "SYSIBM"."SQLSCHEMAS"."TABLE_SCHEM" NOT LIKE ? ORDER BY "SYSIBM"."SQLSCHEMAS"."TABLE_SCHEM"]
[parameters: (Unicode(length='Q%'), Unicode(length='SYS%'))]

unicode = str is basically what I had done before (except I search/replaced), and this loads the libraries, just not the tables/files.

[bchoudhary6415]

Hello @jedatcrs Can you please update on this issue, still facing after above code changes?

Thank you

[jedatcrs]

No, I was never able to get any further than my last post above.

[bchoudhary6415]

Hello @jedatcrs Can you please share the steps to reproduce the issue as I'm not able to reproduce it? Actually I want to know how you installed Apache Superset and used in connection environment?

Thank you

[jedatcrs]

I've had to move on to different projects, but most of that is outlined in my first post. At the time, Superset had recommended installing itself as a package ( pip install apache-superset ), however, looking at the instructions today, it looks like they've modified the recommended install instructions. Once installed, I just went to the "Connect Database" section and started trying to add my IBM i/AS400. @bchoudhary6415 are you saying that in your environment, ibmdbsa works perfectly fine with an IBMi/AS400? If so, I may spin up a new VM and try again.

[rfx77]

@jedatcrs i invested some time to try to use this library but even if you can get it to work this is never close to be production-ready anyway.

[bchoudhary6415]

@jedatcrs i invested some time to try to use this library but even if you can get it to work this is never close to be production-ready anyway.

@rfx77 You mean to say that, connection started working for you, but not able to run operations, like CRUD operations etc.?

[bchoudhary6415]

I've had to move on to different projects, but most of that is outlined in my first post. At the time, Superset had recommended installing itself as a package ( pip install apache-superset ), however, looking at the instructions today, it looks like they've modified the recommended install instructions. Once installed, I just went to the "Connect Database" section and started trying to add my IBM i/AS400. @bchoudhary6415 are you saying that in your environment, ibmdbsa works perfectly fine with an IBMi/AS400? If so, I may spin up a new VM and try again.

@jedatcrs Can you please try once and give me some sample repo so that I can run in my environment and try to reproduce the issue, like after connection how you are using Apache superset?

[jedatcrs]

I was just trying to connect to the database using the connect string mentioned above. IBM_DB_SA itself is stock 0.4.0 with the handful of changes made above. With those changes, I can connect, and go to sqllab and see my libraries under "SCHEMA", but I'm unable to get any of the tables to list, presumably under "TABLE SCHEMA". The error messages in superset aren't very clear at this point, and is only complaining about HTTP code 422 (Unprocessable content).

[bchoudhary6415]

Hello @jedatcrs The error HTTP code 422 (Unprocessable content) seems some general error. If you can share some repo, so that I can proceed and debug the code. Are you using this "ibm_db_sa+pyodbc://username:password@ipaddress/machinename" connection string?

[jedatcrs]

No, I'm using: ibm_db_sa+pyodbc400://username:password@ipaddress/machinename Below is exactly the same at 0.4.0 but with the modifications @rfx77 and I have tried to make it work with an IBM i/AS400 https://drive.google.com/file/d/111maPFqymJo3qM8sYL4vWsRBZOrUb7qt/view?usp=sharing

[jedatcrs]

FYI, I just tried the package sqlalchemy-ibmi, with my environment, and it seems to be working well with our IBM i and Superset. I'm able to retrieve libraries and files, and build SQL queries.
https://github.com/IBM/sqlalchemy-ibmi