After about a year and half, I am finally able to connect to the DB2 database we have through dBeaver. The connection is successful as a LUW (Our db2 is z/os). I was able to get the drivers required after installing IBM Data Studio.
Once I am connected, I go down the schema, get to Tables, and on clicking that, I get the below error.
SQL Error [42704]: SYSCAT.SCHEMATA IS AN UNDEFINED NAME. SQLCODE=-204, SQLSTATE=42704, DRIVER=3.69.56
SYSCAT.SCHEMATA IS AN UNDEFINED NAME. SQLCODE=-204, SQLSTATE=42704, DRIVER=3.69.56
THE DESCRIBE STATEMENT DOES NOT SPECIFY A PREPARED STATEMENT. SQLCODE=-516, SQLSTATE=26501, DRIVER=3.69.56
THE CURSOR SQL_CURLH200C1 IS NOT IN A PREPARED STATE. SQLCODE=-514, SQLSTATE=26501, DRIVER=3.69.56
SQL Error [42704]: SYSCAT.SCHEMATA IS AN UNDEFINED NAME. SQLCODE=-204, SQLSTATE=42704, DRIVER=3.69.56
However, if ignore the error and go to New SQL query and write a simple
Select * from schema.table
it works fine and get the results I want.
Considering the time i have spent to get till here, this is sufficient, but to deploy as a solution in my department, I need to be able to look at a Table List (schema).
Any help would be awesome.
EDIT1: What the issue is here, is that there is no SCHEMA with the name SYSCAT and no table named SCHEMATA.
The z/OS Db2 catalog has different names than the ones used on Db2 on distributed (Linux Unix Windows aka LUW). Here is a list of objects on Db2 z/OS that you can review.
It looks like you are using dBeaver to navigate through a UI the objects on Db2 for z/OS. You will need to ensure you have a db2 jcc driver that is for z/OS Db2. It looks like you may be using one from LUW as the SYSCAT.SCHEMATA is an LUW object, not a z/OS object.
Your other query works because you are specifying a known table name. Other queries should be fine. The issue is the interface in dbeaver is looking at Db2 system objects for LUW and not z/OS. This will continue until you are able to resolve the driver issue.