Endless troubles with setting up DB2 and IBM Data Studio

Question

I have to use DB2 for a course at my faculty. I'm on Windows 10, installed DB2 express C, installed Data Studio. During the installation I've used 'db2admin' as the username and 'admin' as the password. Then I ran a series of SQL scripts provided by our faculty to generate all the tables that we need and to populate them. That apparently worked as well.

Here's the problem. All the tables were made under that schema 'aleksa' which happens to be my computer username.

From the command line I can do everything just fine. Data studio is the problem. When connecting to the database I use the uppermentioned db2admin and admin as the credentials. At first, when I tried to run any query I kept getting some authorization errors. So then I googled for a while and found that I needed to grant access to db2admin on those tables (why an admin account needs granting of anything beats me). So I've done that by connecting to the database from the command line (by the way, the command line never requires me to enter any credentials whatsoever), and running a series of

grant select on <table_name> to user db2admin

I've no idea whether I should have but I also, just in case, ran

grant all on <table_name> to user db2admin

all of those, of course, for each table that I have.

And each one of them ran successfully. After doing that I no longer get the authorization error in Data Studio when running queries. But I also don't get anything.

To repeat, running queries from the command line works fine which is why I know that the database is fine and that I actually have data in it. Which is also why I know that the output for the very same query ran from Data Studio instead of the command line shouldn't be just the query execution time and no results in the 'SQL results' window. In my case the queries that I've tried were simply 'select * from aleksa.table_name'. They produce a bunch of rows in the command line, but none in Data Studio.

So, any ideas?

Answer 1

The Db2 is working as designed.

Your question is not about programming, but instead it is about administration and operational aspects.

You may experience 'endless troubles' if you don't have basic education in Db2.

Consider getting some education in Db2 before using Db2 , or studying the free online Db2 Knowledge centre for your Db2 version, until you understand the required concepts.

Stakoverflow is not a substitute for education

If you connect (in DataStudio) with the same credentials that created the objects, then queries that reference those objects will not get access errors (by default), and will see data that exists in the objects.

If you don't want the schema-name to be 'aleksa' and you do want the schema name instead to be 'db2admin' then run the scripts again whilst connected to the database as db2admin (and account aleksa can additionally first drop all the existing objects in the alesksa schema if both schemas do not fit).

If you connect with different credentials than those of the creator, you may get access errors depending on what the scripts have done. We cannot see what the scripts have done, they may have applied non-default permissions on the tables, or used roles, or require certain group memberships etc. We also cannot see how your Db2 is configured. Presumably you are able to see such details.

The db2admin account is not (by default, in current versions of Db2) the SECADM, so db2admin does not get data access, and the account defined as the SECADM is able to grant data-access and accessctrl authorities to other accounts.

If your queries fully qualify the object names (i.e specify a schema and a table name), and those objects exist, and the connecting-account has SELECT access, and the object contains data, then DataStudio will show data (or an error).

Study the relevant pages of the Db2 documentation online for more details.

There is nothing special about DataStudio with Db2, apart from the fact it covers all Db2 functionality on all platforms. IBM Data Studio is a java application, and many other similar tools exist with lesser coverage of advanced Db2 functionality.