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.

Clients:

  • DBeaver 5.2.5 (my preferred client) (very)
  • Squirrel SQL 3.8.1
  • SQL Workbench 124
  • IBM Data Studio 4.1.3

Drivers:

  • JTOpen 6.6
  • JTOpen 7.6 (with recent download of IBM Data Studio)
  • JTOpen 9.5

2 answers

  • answered 2018-12-05 20:34 Charles

    ODBC/JDBC use a different set of catalogs...located in the SYSIBM schema...

    sysibm.sqltables
    sysibm.sqlcolumns

    ect...

    ODBC and JDBC Catalog views

  • answered 2018-12-05 21:49 Dam

    With SquirrelSQL 3.9 and JtOpen, you have to select two options in the driver properties:

    remarks = true
    extended metadata = true
    

    In new session configuration, check SQL / Display metadata, and voilĂ  : Squirrel with metadata in column names

    Checked with V7R1, with DDS comments or SQL Labels