SAP/DB2 connections via ODBC - How to limit the number of visible tables

[Former Member]

When accessing DB2 via ODBC for, say, some Z-table information I find that some 3rd party apps like UPS Worldship cannot handle the number of tables returned. This was also true with SQLServer BTW.

Question:

Is it possible to limit the number of tables that are returned by DB2 via the ODBC driver for Windows?

I log in with a user that only has rights on 8 tables. I was hoping that applications like MSAccess and UPS Worldship would only see those 8 tables when using that specific DB2 login. That would be "problem sovled" but it seems not to be the case. Even MSAccess lists ALL of the tables but only lets you use those you have access to. Certinaly there must be a way to hide the table names from a user that has no access to them.

BTW: This gets to be a huge problem in ERP6 because the number of tables jumps dramatically from 4.6b where I am today.

Thank you for any tips.

Crew

[Former Member · Accepted solution]

Hello Crew,

You can specify a CLI/ODBC configuration keyword TABLETYPES to limit the number od tables returned by the application.

This option can be used in conjunction with DBNAME and SCHEMALIST to further limit the number of tables for which information will be returned.

TableType is used to provide a default for the DB2 CLI function that retrieves the list of tables, views, aliases, and synonyms in the database. If the application does not specify a table type on the function call, and this keyword is not used, information about all table types is returned. If the application does supply a value for the tabletype on the function call, then that argument value will override this keyword value.

Please see the following link:

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.apdv.cli.doc/doc...

Regards,

Adam