ODBC connections not listing tables

william giannelli

ODBC connections not listing tables

We are having applications connecting to DB2 for z/OS through ODBC not able to see a drop down list of tables.

They are not getting a connection error, they just can not see the tables.

One application is using SAP Crystal Reports the other application is using Access via a AWS workspace.

Any clues here?

thanks

Bill

bernd oppolzer

ODBC connections not listing tables
(in response to william giannelli)
I'm no expert regarding ODBC, but somewhere in the ODBC connection there
must be
some sort of information regarding the remote end of the database
connection,
so that ODBC knows, which kind of database it is referring to,
especially, where to
read the catalog information from.

For example:

with DB2 for z/OS etc., the catalog qualifier is SYSIBM; the table name
is SYSTABLES
but for DB2 for VM/VSE, it is SYSTEM, IIRC, table name maybe SYSCATALOG
(too long gone)
much more differences for Oracle ...

I guess, if you have the wrong setting somewhere in the middleware, ODBC
will not be
able to show the table names.

Or: a authorization problem, although the catalog tables should be
readable by PUBLIC,
normally.

HTH, kind regards

Bernd



Am 03.09.2019 um 23:46 schrieb william giannelli:
>
> We are having applications connecting to DB2 for z/OS through ODBC not
> able to see a drop down list of tables.
>
> They are not getting a connection error, they just can not see the tables.
>
> One application is using SAP Crystal Reports the other application is
> using Access via a AWS workspace.
>
> Any clues here?
>
> thanks
>
> Bill
>
>
> -----End Original Message-----

william giannelli

RE: ODBC connections not listing tables
(in response to bernd oppolzer)

our Development and TEST work fine. So there must be something on the production z/OS side that is configured wrong.

Thanks

Bill

Peter Vanroose

Re: ODBC connections not listing tables
(in response to william giannelli)

Then it's almost certainly an authorization issue.

You could verify this by connecting to the (production) server with the same user ID, but through an other channel than ODBC (like e.g. the db2 command line client). Then issue the following SQL statement and inspect the SQLCODE (and the output):

      SELECT count(*) FROM sysibm.systables

If you're getting an SQLCODE -551, someone should grant SELECT on table sysibm.systables to your user ID (or indeed maybe to PUBLIC, as was suggested earlier).


In Reply to william giannelli:

our Development and TEST work fine. So there must be something on the production z/OS side that is configured wrong.

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://abis.be/

william giannelli

Re: ODBC connections not listing tables
(in response to Peter Vanroose)

thank you both for your responses!

So far the authorizations seem correct Catalog tables and Store Procedures granted to public (select , execute).

We reinitialized the Catalog Stored Procedures in the system that is not working. So I believe something is not configured right for the Catalog Stored Procedures and packages.

thanks

Bill 

John Bucaria

ODBC connections not listing tables
(in response to william giannelli)
Is the ODBC connection string specifying the correct schema for all subsystems?

From: william giannelli <[login to unmask email]>
Sent: Wednesday, September 04, 2019 7:16 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: ODBC connections not listing tables


thank you both for your responses!

So far the authorizations seem correct Catalog tables and Store Procedures granted to public (select , execute).

We reinitialized the Catalog Stored Procedures in the system that is not working. So I believe something is not configured right for the Catalog Stored Procedures and packages.

thanks

Bill

-----End Original Message-----

william giannelli

RE: ODBC connections not listing tables
(in response to John Bucaria)

it seems the application is using a v9.7 ODBC driver. Could this be the issue?

thanks

Bill

J&#248;rn Thyssen

RE: ODBC connections not listing tables
(in response to william giannelli)

Hi Bill,

Is it also V9.7 on the systems that connect to dev and test successfully?

V9.7 GA'ed 10 years ago and have been out of support for a few years, so if you end up opening a PMR you would be told to upgrade.

If you have a Db2 monitor you may try to capture what's happening on the z side, including any negative SQL codes from authorization issues, stored procedures not running, etc.

You can also enable ODBC trace on the client side. 

I ran an SQL trace with IBM Omegamon Db2 while accessing Db2 from Excel.
I notice a number of packages being accessed:

DSNACOLU
DSNAFNKU
DSNAPRKU
DSNATBLU

In addition, a number of procedures are being called:

SQLTABLES
SQLCOLUMNS
SQLPRIMARYKEYS
SQLFOREIGNKEYS

There are several other SQL% procedures in the SYSIBM. schema. You might want to check if they are all running and that all associated DSNA% packages are valid and operative.


In Reply to william giannelli:

it seems the application is using a v9.7 ODBC driver. Could this be the issue?

thanks

Bill



 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

2019 IBM Champion.

Views are personal. 

J&#248;rn Thyssen

RE: ODBC connections not listing tables
(in response to Jørn Thyssen)

You can issue a call to the procedures to verify they are running. See attached screenshots for an example using IBM Db2 Administration Tool. 

 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

2019 IBM Champion.

Views are personal. 

Attachments

  • 2019-09-06 10_14_56-S1 - rs01.png (19.7k)
  • 2019-09-06 10_15_06-S1 - rs01.png (13.5k)
  • 2019-09-06 10_15_17-S1 - rs01.png (21.5k)

william giannelli

RE: ODBC connections not listing tables
(in response to Jørn Thyssen)

thank you for all your help!!!!

yes, we have been looking at the very same packages and Stored Procedures. So far we have not been able to find a difference and to emulate the issue in our sandbox. we found one "remote" package being called "SYSSH100" that is pretty old (for UDB/LUW v9). Also I see differences for the ODBC packages from job DSNTIJCL, collid DSNAOCLI. The DESCSTAT for those packages are blank for systems working NO for the one not working.

thanks

Bill 

J&#248;rn Thyssen

RE: ODBC connections not listing tables
(in response to william giannelli)

Did someone REBIND with DESCSTAT(NO)?

I would REBIND the packages with DESCSTAT(YES).

In Reply to william giannelli:

thank you for all your help!!!!

yes, we have been looking at the very same packages and Stored Procedures. So far we have not been able to find a difference and to emulate the issue in our sandbox. we found one "remote" package being called "SYSSH100" that is pretty old (for UDB/LUW v9). Also I see differences for the ODBC packages from job DSNTIJCL, collid DSNAOCLI. The DESCSTAT for those packages are blank for systems working NO for the one not working.

thanks

Bill 



 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

2019 IBM Champion.

Views are personal.