ContentAssistant does work right with real Ibmi-DB2 SQL-Tables (metadatasource=0/1 and naming=system)

[PhilippeBrossel]

System information:

• Operating system (distribution) and version === Microsoft Windows 10 Enterprise - Version 10.0.19042 Build 19042
• DBeaver version === Version 21.0.3.202104181339 - Released 19 April 2021
• Additional extensions === NO, standalone DBeaver instance - Same behaviour in the Eclipse IDE.

Connection specification:

• Database name and version === Ibmi DB2 V7R4 - Latest TR
• Driver name === DB2 iSeries/AS 400 - net.sf.jt400:jt400:RELEASE - Version 10.6
• Do you use tunnels or proxies (SSH, SOCKS, etc) === NO

Describe the problem you're observing:

• DBeaver is really a very very good tool to work with databases. Many thanks !

• I read thru the docs and issues, but did not see what I'm doing wrong.

• I would like to actively contribute to find solutions/document/test because we are currently moving to 'real' SQL-Tables instead of PFs and LFs (legacy physical and logical files) and the issues have mostly to do with these new tables.

• I can use the Ibmi system I develop on to carry out tests. I cloned the repo to start debugging.

• I think all the issues have to do with these driver settings :

  • naming="system" meaning that the DB-objects are searched in a list of schemas (Libraries) instead of only the one set with SET SCHEMA
  • metadatasource="0" (Retrieve Object Information) or "1" (SQL stored procedures) or "-1" (??)

• Just wanna give an overview of the issues here. I can split them apart later on into separate bugReports for easier handling and supply more details.

• Issue 1 : Table properties do not show ANY columns for real SQL-Tables :

  • 
  • Note that an SQL-Script shows the right metadata

• Issue 2 : Table properties for legacy PFs do not show column descriptions when metadatasource="1"

  • 
  • 

• Issue 3 : Content assistant in SQL script does only find tables when a schema is set (is against naming=system)

  • 
  • 

• Issue 4 : Content assistant in SQL script does not show columns for real SQL-Tables

  • does not show for real SQL-Tables

  • does show right for legacy PFs

Steps to reproduce, if exist:

Include any warning/errors/backtraces from the logs

[kseniiaguzeeva]

Sorry for the long responce. I cannot reproduce the issues on DB2 LUW. I will try to find out something.

[PhilippeBrossel]

Sorry for the long responce. I cannot reproduce the issues on DB2 LUW. I will try to find out something.

No problem. In the meantime, I cloned the repo and could find explanations/fixes for some of the issues.

I would like to open separate issues for better readability and contribute my changes as a starting point for enhancements/discussions.

Is that OK for you ?

Here a summary of my findings :

• DB2 is the common name for Ibm DB-products, but they differ significantly. That's the reason why it is difficult to reproduce issues on another platform :

  • DB2 for i is the product that I use (runs on IbmI, formerly known as AS/400 )
  • DB2 for Linux/Unix/Windows
  • DB2 for z/os runs on mainframes

• About Issue 1 : Table properties do not show ANY columns for real SQL-Tables :

  • AS400JDBCDatabaseMetaData driver is very sensitive what regards case
  • lowercase table names are automatically uppercased if not surrounded by "" thus misleading the driver

• About Issue 2 : Table properties for legacy PFs do not show column descriptions when metadatasource="1"

  • nothing found yet

• About Issue 3 : Content assistant in SQL script does only find tables when a schema is set (is against naming=system)

  • DB2 for I works with a concept of library List that is a list of schemas (instead of just 1 schema)
  • DBeaver currently does not support that.

• About Issue 4 : Content assistant in SQL script does not show columns for real SQL-Tables

  • same reason as issue 1

[Matvey16]

Thank you for your investigation. We don't have a fix for it as of now. If you would like to contribute by doing a pull request we will gladly review it.

[PhilippeBrossel]

OK, that's fine. Many thanks. You can close this issue here since it mixes different points. I will open more specific ones to hopefully contribute something useful.