We have a 3rd-party DB2/400 application that's the core of our business. It was recently migrated from our private server with DB2/i v6r1 on Power7 to a hosted cloud service with DB2/i v7r3 on Power9.
Since the migration, JDBC-based SQL clients cannot see the labels of tables or columns when browsing the db in whatever sort of database explorer they have. In most cases, the text is supposed to show up in the "Remarks" or "Description" column of the explorer, but it no longer does.
Even in IBM Data Studio it does not work with the new server, but it does provide the table label on the summary page of each table.
I tried the following in the JDBC settings, but they did not help:
remarks = sql (also tried "system") extended metadata = true
What DB2 server settings might be involved in causing this problem with JDBC SQL clients..?? I've searched the IBM website, but the mountains of answers are overwhelming.
To be clear, what I'm looking for is the labels from the DDL statements, such as these:
LABEL ON TABLE "SCHEMA1"."TABLE1" IS 'Some Table Description'; LABEL ON COLUMN "SCHEMA1"."TABLE1"."COLUMN1" IS 'Some Column Desc';
The clients don't see the labels, yet the following SQL queries are able to do so:
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TEXT FROM QSYS2.SYSTABLES WHERE TABLE_SCHEMA = 'SCHEMA1' AND TABLE_NAME = 'TABLE1' SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_TEXT FROM QSYS2.SYSCOLUMNS WHERE TABLE_SCHEMA = 'SCHEMA1' AND TABLE_NAME = 'TABLE1'
I've tried the clients and drivers listed below, and none of them can access the labels for tables or columns on the new server. But..with the same drivers and same settings, they can see the labels on the old server just as they always have. So what's different..? I've read many posts on StackOverflow and elsewhere, and tried many tweaks of settings in the clients and drivers, but nothing has worked. It seems clear this is an issue on the new server.
- DBeaver 5.2.5 (my preferred client) (very)
- Squirrel SQL 3.8.1
- SQL Workbench 124
- IBM Data Studio 4.1.3
- JTOpen 6.6
- JTOpen 7.6 (included with the recent download of IBM Data Studio)
- JTOpen 9.5