After database migration to new DB2/400 server, the table and column labels are no longer accessible. What server settings to enable..?
We have a 3rd-party DB2/400 application that's the core of our business. It was recently migrated from our private server with AS400/i v6r1 on Power7 to a hosted cloud service with AS400/i v7r3 on Power9.
Since the migration, SQL clients cannot see TABLE_TEXT or COLUMN_TEXT when browsing tables in whatever sort of database explorer they have. In most cases, the text is supposed to show up under "Remarks" or "Description" when browsing tables or columns in the explorer, but it no longer does.
Even the IBM Data Studio won't show the data in columns, but it does provide the information buried deep and inconvenient to access.
What DB2 Server settings are involved in providing this metadata to SQL clients..?? I've searched the IBM website, but the mountains of answers are overwhelming.
I would like to be fore-armed with this information before I discuss the issue with our hosting provider. They provide the ODBC/JDBC connection "mostly unsupported", but I'm hoping they'll consider helping us with this issue if I can describe the server settings with as much detail as possible.
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 may not access 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. 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 (with recent download of IBM Data Studio)
- JTOpen 9.5
ODBC/JDBC use a different set of catalogs...located in the
With SquirrelSQL 3.9 and JtOpen, you have to select two options in the driver properties:
remarks = true extended metadata = true
Checked with V7R1, with DDS comments or SQL Labels