Table List in Excel

Steve Grimes

Table List in Excel
Hello, DB2 V7.1, OS 390 2.10 here!

I'm trying to set up a user to be able to retrieve data from Excel. We've
created a RACF group and GRANTed SELECT authorities in DB2 to this group.
(The user's default RACF group is this new group.)

I have MS Query installed and have successfully used the "Get External
Data" option for my tables, using my "mighty dba" password. (Again, using
RACF groups with authorities granted to the groups. The big difference is
that my RACF group is the owner of the tables.)

When I connect as the user, things seem to work fine, except that I can't
get a list of tables to show up inside the MS Query "Add Tables" box. (I
can write the SQL directly, but I was hoping the user could use the GUI Sql
builder.)

I've authorized the new RACF group with select authority against SYSTABLES,
but that didn't change anything.

Any suggestions?

Thanks!

Stg

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

[login to unmask email]

Re: Table List in Excel
(in response to Steve Grimes)
Steve,

This is list of catalog tables that MS Query needed access to in order to
function properly.

SYSCOLAUTH
SYSCOLUMNS
SYSDATABASE
SYSFOREIGNKEYS
SYSINDEXES
SYSKEYS
SYSPROCEDURES
SYSRELS
SYSSYNONYMS
SYSTABAUTH
SYSTABLES


Carol Sutfin
Corporate DBA
AmSouth Bank
(205)261-5214
[login to unmask email]




[login to unmask email]
L.WUSTL.EDU To: [login to unmask email]
Sent by: "DB2 Data cc:
Base Discussion Subject: Table List in Excel
List"
<[login to unmask email]
G>


01/13/2004 09:47 AM
Please respond to
"DB2 Database
Discussion list at
IDUG"





Hello, DB2 V7.1, OS 390 2.10 here!

I'm trying to set up a user to be able to retrieve data from Excel. We've
created a RACF group and GRANTed SELECT authorities in DB2 to this group.
(The user's default RACF group is this new group.)

I have MS Query installed and have successfully used the "Get External
Data" option for my tables, using my "mighty dba" password. (Again, using
RACF groups with authorities granted to the groups. The big difference is
that my RACF group is the owner of the tables.)

When I connect as the user, things seem to work fine, except that I can't
get a list of tables to show up inside the MS Query "Add Tables" box. (I
can write the SQL directly, but I was hoping the user could use the GUI Sql
builder.)

I've authorized the new RACF group with select authority against SYSTABLES,
but that didn't change anything.

Any suggestions?

Thanks!

Stg

---------------------------------------------------------------------------------

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org
. The IDUG List Admins can be reached at [login to unmask email]
Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Steve Grimes

Re: Table List in Excel
(in response to csutfin@AMSOUTH.COM)
Thanks Carol,

I did SELECT GRANTs for these tables, but the Excel Table list is still
blank. (If I check the box for System tables, I do see many of those.)
My gut feeling is that I'm being shot down by excel because the ID I'm
using, belongs to a group that is not the owner/creator of these tables.

Oh well, it looks like we'll be teaching a little SQL to our user...

Thanks again!

Stg

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Glenn mackey

Re: Table List in Excel
(in response to Steve Grimes)
Here is an idea ... Check the db2 connect (configuration assistant), in the
enterprise tab, and the values for SCHEMALIST etc. to determine if the
blockage is there. Put the table creator in the schemalist.



-----Original Message-----
From: [login to unmask email]
[mailto:[login to unmask email]
Sent: Tuesday, January 13, 2004 9:48 AM
To: [login to unmask email]
Subject: Table List in Excel


Hello, DB2 V7.1, OS 390 2.10 here!

I'm trying to set up a user to be able to retrieve data from Excel. We've
created a RACF group and GRANTed SELECT authorities in DB2 to this group.
(The user's default RACF group is this new group.)

I have MS Query installed and have successfully used the "Get External
Data" option for my tables, using my "mighty dba" password. (Again, using
RACF groups with authorities granted to the groups. The big difference is
that my RACF group is the owner of the tables.)

When I connect as the user, things seem to work fine, except that I can't
get a list of tables to show up inside the MS Query "Add Tables" box. (I
can write the SQL directly, but I was hoping the user could use the GUI Sql
builder.)

I've authorized the new RACF group with select authority against SYSTABLES,
but that didn't change anything.

Any suggestions?

Thanks!

Stg

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

This message and accompanying documents are covered by the Electronic
Communications Privacy Act, 18 U.S.C. §§ 2510-2521, and contains information
intended for the specified individual(s) only. This information is
confidential. If you are not the intended recipient or an agent responsible
for delivering it to the intended recipient, you are hereby notified that
you have received this document in error and that any review, dissemination,
copying, or the taking of any action based on the contents of this
information is strictly prohibited. If you have received this communication
in error, please notify us immediately by e-mail, and delete the original
message.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Rich Gugel

Re: Table List in Excel
(in response to Glenn mackey)
If you are running version 8 of db2 connect you may be using the new
getmetadata functions see (UQ72083). Check and see if they are granted
execute to the user.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Steve Grimes

Re: Table List in Excel
(in response to Rich Gugel)
Hello again!

I think I'm using version 7.2 of DB2 Connect, PE.

Playing with the Enterprise Tab Schemalist did it! I had SYSIBM, but not
the other owner name. Adding that, solved the problem.

Thank you Glenn et. al.!

Stg

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm