#1534 DB2/Object Tree Issue

[Paul Stephenson]

I looked at previous tickets and did all kinds of searching and have involved our DBA as well, but we still have this issue and need someone who can explain why Squirrel in 1 particular situation will NOT display the object tree of all the schemas/tables/views in that database. Ticket #1523 created in November of 2023 asked the same question, but the 1 answer to just look at the alias isn't a good answer as that's not the issue.

We have 3 separate DB2 databases(running DB2 for Z/OS), all on the same version of DB2 v13, which was recent, but this problem was there before the change. We use the new 4.31 jdbc db2 driver just like is instructed.
Our prod and warehouse databases have no issue. It's only our test database that exhibits the issue.

Please note, that I have an instance of Squirrel 3.7.1 which uses the same db2 driver and it can connect and display the object tree of ALL 3 databases including test.

Anyone running Squirrel 4.x (with new Java 11 or greater) all have the same problem on every single instance where the test database object tree will NOT display. Squirrel 3.7.1 will only run on Java 8 and that is going away, so staying on an old version of Squirrel is not the long term solution. And, I tried all the stable versions I could in the 4.x world, including 4.8 the current stable version. This problem consistently remains.

We took the advice of the AI masters that gave us suggestions such as adding parameters to the string such as:
retrieveMessagesFromServeronGetMessage=true
emulateParameterMetaDataForZCalls=1

I used a lot of other parameters as well, but nothing worked.

What is really odd about this situation is:
1) Squirrel 4.x can display the object tree of our prod & warehouse databases with no parameters added, only a similar jdbc url string that test has
2) Squirrel 3.7.1 CAN display the object tree of TEST and prod and the warehouse with no issues.

What changed in Squirrel 4.x where it now has an issue displaying the test object tree when it's 3.7.1 counterpart didn't have this issue?
Can this be corrected so we have no issues with any 4.x version against any of our DB2 databases?

[Gerd Wagner]

Sorry, I can't reproduce your problem. I created a SQuirreL installer that writes some extra log entries which might help to find the problem. The installer can be found here:
https://sourceforge.net/projects/squirrel-sql/files/reproductions/bug1534/

With this version you may open the Alias that has the problem. I expect it to create lines in the logs which contain the string "#1534:". If you send me the log file I might be able to find out what the problem is.
Note that this version requires at least Java 17.

[Paul Stephenson]

Gerd,
Thanks for your quick reply!
I’ve installed that snapshot on my pc and I am running a Java 17 instance on that machine.
I’m attaching the 2 log files that I could get to write out, jdbc-debug.log and squirrel-sql.log.
Unfortunately, I’m not seeing any 1534 entries in the log files.
But, I hope what I provide helps AND if you want me to run another snapshot to capture more of the issue, please let me know.
Thanks!
Paul Stephenson

[Paul Stephenson]

here's the other log file, I had trouble uploading in 1 post

[Gerd Wagner]

I uploaded another installer to the same place. This time I certainly expect to see entries containing "#1534:" because they are issued immediately after an Alias gets connected to.

FYI: I don't need the jdbcdebug.log but just the squirrel-sql.log.

[Paul Stephenson]

Gerd,
Thank you again for replying so quickly. I pulled the new version you uploaded and ran another test. You are correct, this time there is a BUNCH of #1534 records in the log file this time and from it, you can see it can read the schema names, but it doesn't go down into the tables and other objects in each schema.
I've attached the log file as you requested.
Please let me know when I can run another test if you need me to do so and thank you so much for your help to figure out this issue!
Paul

[Gerd Wagner]

I'm just uploading another snapshot with more "#1534:"-logs to the same location.

The last logs that show that your database has more than 3100 schemas. You may try out reducing these. You can do that in the dialog which opens when you click the "Properties" button on the Alias dialog.

[Paul Stephenson]

Ok, Gerd, I've completed the assignment. I modified the alias through the properties button.
I chose "Load schemas filtered by name" & entered "PEGA%,SBRGWC%,REF", which should have limited the loaded schemas to 8 schemas: PEGA,PEGAQA,PEGAT,REF,SBRGWC,SBRGWCQA,SBRGWCT,SBRGWCX.
That is all that shows in the in the list of schemas in the objects frame, but I noticed the #1534 shows a whole lot more schema names listed in the log file. I just wanted you to know I did update the properties as you requested.

Thanks again for all your help on this.
Let me know when you want me to test another snapshot.
Paul

[Gerd Wagner]

Paul, the schemas you see in the logs are all schema names which are always loaded. The filtering applies to the contents of the schemas only.

I'm just uploading another installer with again more "#1534:"-logs. The logs concentrate on table loading. So my assumption is that the schemas which SQuirreL displays empty actually have tables. Is that right?

[Paul Stephenson]

Gerd - sorry for the delay, I've been out of office and just getting back to catch up on our testing.
Thanks for replying with a new version to use in a test. I believe I have limited the loading only to a handful of schemas. For those schemas, the do have tables that should be displayed.
The schemas I specified to load, as mentioned previously are:
PEGA,PEGAQA,PEGAT,REF,SBRGWC,SBRGWCQA,SBRGWCT & SBRGWCX
All of these schemas have tables that should be display. Thx.

[Gerd Wagner]

No problem, Paul, I'm just uploading another version.

It might be that something unexpected happens when table types are loaded from your database. If you should at least see the Object tree's top node please click it and then look at the "Table Types" tab on the right. My guess for now is that it is empty.

FYI: I added one functional change in this version which might even make tables appear. It could also cause some unexpected behavior.

[Paul Stephenson]

Hey Gerd!
I have good news! I have clicked the object trees on every test, but this time, as you anticipated, I DO see the tables AND if I click on a table, I see the column details and other details on the tabs on the right! I didn't notice any unexpected behavior with my small amount of testing. But, this is great progress! What do you think the next step should be? Do you have more changes for me to test? Eventually, I will want to recommend a stable version with the fix to this issue to our IT support to include for user downloads. So I will look to your advice on which one to specify.
Thanks so much for your continued efforts in resolving this issue!
Paul

[Gerd Wagner]

Good to hear, Paul, that already the first guess was a hit. May I ask for the following three things:

1. I just uploaded another version to the same place as before with a proper implementation of the changes and less logging. Could you try if it still works?
2. Click on any of the now loaded tables in the Object tree then check the tab named "Info" on the right an let me know if it displays a Property name "type". If so please tell me its "Value".
3. In case you or one of your colleagues knows some Java please adjust the attached small Java program to your databases data. Then compile and run it and let me know its output.

Thanks Gerd

[Paul Stephenson]

Hey Gerd,
I just recalled I never actually came back and did your last test you requested as I thought I had, so sorry about that! So, per your last request
1) I uploaded the newest copy of the app you gave me and it worked just like it did before. I can provide a log file again if you want it.
2) I clicked on several tables and on the "Info" tab, under property name "Type", the "Value" is "TABLE" for every one that I clicked.
3) I'm not a java developer and I just asked some folks this afternoon around my team and on some other teams for help. So, I know what we need to populate in the variables you left in your snippet, but I need someone with a java IDE to include the db2 driver in their classpath and compile it for me. I will provide another update as soon as I can get this completed. but I wanted to at least give you an update and let you know I am working on your last request.
Thanks!
Paul

[Gerd Wagner]

Thanks for the information, Paul. I just committed the changes you tried out.

Your information bear no surprises considering the tests we did.

[Paul Stephenson]

Hey again Gerd,
I got someone to run the java pointed to the database with the issues we have been working on and they said that no results were returned.

Just to be sure they were doing it right, they did point it against one of our DB2 databases that does work, that we can "DB2" and they got the following results:
tableTypeName = ALIAS
tableTypeName = AUXILIARY TABLE
tableTypeName = GLOBAL TEMPORARY TABLE
tableTypeName = MATERIALIZED QUERY TABLE
tableTypeName = SYNONYM
tableTypeName = SYSTEM TABLE
tableTypeName = TABLE
tableTypeName = VIEW

Hope this helps and just let me know when you have something else to test and I'll run it and give you more results.
Thanks!
Paul

[Gerd Wagner]

After our tests this result is what I expected.
The reason why SQuirreL did not show tables for the database with the issues, was that no results were returned by the test program.

If you'd be interested you could contact DB admins and ask them why no results are returned by the test program.

I just released a new snapshot with the changes I made for you. It is available here
https://sourceforge.net/projects/squirrel-sql/files/3-snapshots/snapshot-20250815_2152/
or here
https://github.com/squirrel-sql-client/squirrel-sql-snapshot-releases/releases/tag/snapshot-20250815_2152

The changes will be included in the upcoming 5.0.0 release.

Gerd

[Paul Stephenson]

Thanks Gerd! I did load the latest snapshot and confirmed that it is working as the prior iterations have been. Also, I reached out to our DBA sharing the test java app and asked if he can explain why the test database has the issue but other DB2 dbs do not, just like Squirrel exhibited.
If the DBAs come back with a response, I will let you know.
But, thank you for finding a workaround that populates the object tree regardless of this config issue/bug. Assuming we never get the test database modified to work on any version of Squirrel, I plan to let it be known the 5.0.0. upcoming release is going to have a fix for this in it that we can target to have available for all users to load on their systems. Thanks again for all your help on this!

[Paul Stephenson]

Hey Gerd, I did end up working with the one that actually installs DB2 software on our mainframe system, David. Turns out they had tried to get around this issue over 4 years ago with no success. At the time, I don't think they was able to get any support from Squirrel support although I don't know how they tried to reach out. So thank you again for responding to me when I started this ticket.

David actually compared our working DB2 installations (DB2 & WDB2) to the not working version TDB2. He couldn't find any differences in setup/config or anything. The ONLY thing that is a big difference is the size of the schema list and tables in TDB2 vs DB2 or WDB2. Our test system has much more than production or the warehouse (as we call them). Maybe that's why it returns no results? It blows it out of the water somehow?

Which, btw, we were curious, how did you work around this problem in the code fix you made in the snapshot you gave me? You said this will be in upcoming version 5.0.0? When do you expect that version to be released?

At this point, our solution looks like it is going to have to be wait for what you all call a stable version of 5.x with this fix and then promote that as the version for all of our developers to load and use. Will that be a stable version? If not, how long would you say it would be to have a stable version with that fix in it?

[Gerd Wagner]

You're welcome, Paul.

Concerning how I got around the problem:
There are three rather common table types namely TABLE, VIEW and SYSTEM TABLE. If the database itself doesn't provide any table types I now proceed as if the database provided these types.

Concerning the 5.0.0 release:
I'll wait at least till Java 25 is released. If no bugs or feature request occur which I think should make it into the release it shouldn't be long after the Java release.