IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this forum will no longer be available. More details available on our FAQ.
Topic
  • 4 replies
  • Latest Post - ‏2018-12-10T22:10:09Z by spinjector
spinjector
spinjector
3 Posts

Pinned topic After database migration to new DB2 server, the table and column labels are no longer accessible to JDBC clients..?

‏2018-12-06T19:31:33Z | db2 db2400 db2i jdbc jt400 jtopen

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.

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 (included with the recent download of IBM Data Studio)
 - JTOpen 9.5

 

 



  • Jim_IT
    Jim_IT
    20 Posts
    ACCEPTED ANSWER

    Re: After database migration to new DB2 server, the table and column labels are no longer accessible to JDBC clients..?

    ‏2018-12-06T22:11:08Z  

    This got me too when when we upgraded from 6.1 to 7.1.

    Set the metadata source jdbc driver property to 0. IBM Toolbox for Java JDBC properties

    metadata source

    "0" (ROI access)
    "1" (SQL stored procedures)

     

    Jim

  • Jim_IT
    Jim_IT
    20 Posts

    Re: After database migration to new DB2 server, the table and column labels are no longer accessible to JDBC clients..?

    ‏2018-12-06T22:11:08Z  

    This got me too when when we upgraded from 6.1 to 7.1.

    Set the metadata source jdbc driver property to 0. IBM Toolbox for Java JDBC properties

    metadata source

    "0" (ROI access)
    "1" (SQL stored procedures)

     

    Jim

  • spinjector
    spinjector
    3 Posts

    Re: After database migration to new DB2 server, the table and column labels are no longer accessible to JDBC clients..?

    ‏2018-12-10T15:05:00Z  
    • Jim_IT
    • ‏2018-12-06T22:11:08Z

    This got me too when when we upgraded from 6.1 to 7.1.

    Set the metadata source jdbc driver property to 0. IBM Toolbox for Java JDBC properties

    metadata source

    "0" (ROI access)
    "1" (SQL stored procedures)

     

    Jim

    OUTSTANDING..!! Nailed it..!! Thank you..!! The rightful order has been restored, lol. This DB is massive, and not having those object labels has been a pain in the...brain. Grin

    Ok, since this seems to be an issue with the new hosted server we're on, I have to ask is this "normal"..? In other words, is it just the way v7rX works, or is it more likely to be something that our hosting service inadvertently foobarred with the setup..?

    The reason I ask is because this might be affecting their other customers, and I wanted to let the hosting provider know what the solution is in case anyone asks. Also, can this metadata behavior be altered by a server-side setting, to make it work without JDBC driver properties being altered in the clients..? Basically, make it work the "old way" like in v6rX..? 

    Updated on 2018-12-10T18:13:26Z at 2018-12-10T18:13:26Z by spinjector
  • Jim_IT
    Jim_IT
    20 Posts

    Re: After database migration to new DB2 server, the table and column labels are no longer accessible to JDBC clients..?

    ‏2018-12-10T19:03:23Z  

    I don't believe there is any settings on the IBM i which could change the default setting.

    I initially came to the same conclusions as you did, but after more research I found the metadata source setting.

    Other property Description Required Choices Default
    metadata source Specifies how to retrieve DatabaseMetaData.
    If set to "0", database metadata will be retrieved
    through the ROI (Retrieve Object Information) data flow.
    If set to "1", database metadata will be retrieved by
    calling system stored procedures.
    no "0" (ROI access)
    "1" (SQL stored procedures)
    "0" (V6R1 and earlier)
    "1" (post V6R1)
    Updated on 2018-12-10T19:07:09Z at 2018-12-10T19:07:09Z by Jim_IT
  • spinjector
    spinjector
    3 Posts

    Re: After database migration to new DB2 server, the table and column labels are no longer accessible to JDBC clients..?

    ‏2018-12-10T22:10:09Z  

    Yes, did find that info on my end, but my version is: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzahh/jdbcproperties.htm#jdbcproperties__other 

    The 7.3 (formerly 6.1) pages are the one section of the IBM website I don't get regularly lost in, as is easy to do.

    Anywho, thanks for your help..!! I really appreciate it.