Unfortunately there is no way to use SQL (that I know of) to ask DB2 for all its databases. Perhaps this is a difference between how DB2 is set up/managed versus other databases. A DB2 instance is essentially a database server. It does not do SQL per se as it is not a database. It is the "brains" or "guts" that runs the DB2 logic (and yes, it thus runs SQL, but it is not a database in and of itself). You can create several databases within an instance. But those databases are not stored in a database. Their information is stored in the local database directory, system database directory, and (if remote) the node directory. (Well, and if remote and z/OS or i5/OS, there is also the DCS directory.) This is because the DB2 instance can know about both local and remote databases.
Because of this you have to ask the instance to check its directories for what databases it knows about. This is where you use the non-SQL statements like above.
db2 list db directory will give you the system directory, ie, all databases both local and remote.
db2 list db directory on <path> will give you the local database directory, where local databases reside on disk
db2 list node directory will give you the nodes (ie, the remote servers) that remote databases reside on.
db2 list dcs directory will give you the DCS entries needed for connecting to remote z/OS and i5/OS systems/databases.
So, you unfortunately can't use a database driver as there is nothing to query from database-wise. When you are talking to an instance you aren't necessarily connected to a database. And if you are connected to a database, it won't have information about the other databases, even in its catalog tables, as those are only for the particular database you are connected to.
Now to solve your issue, you could still do this programmatically. You could open a command line prompt (through your code) and if your prompt has DB2 set up in its path (so it can execute the DB2 commands) you could then issue these commands through your code that way. In my past life as a Java developer, I did this with working with some things with DB2 (like dynamically setting certain configuration parameters).
Ok, I think I actually figured out how to handle this. I needed to grant the ability for my DBADM,SECADM to transfer to any ID through the group not the specific ID. In this way I get around the SECADM restrictions.
db2 grant setsessionuser on public to group DB2ADMNS
Since my ID db2admin is in this group, it can now switch to any ID (the public part). If I only wanted to be able to switch to that ID, I could have used:
db2 grant setsessionuser on user bobabob97 to group DB2ADMNS
So now I can perform
db2 set session_user=bobabob97
and it works.
To switch back I only need to run
db2 set session_user=system_user
Best Answer
The concept of a "database" in SQL Server is much closer to a schema in DB2. An entire MS SQL database engine is much closer to a DB2 database:
It's not a perfect comparison; there are a number of differences:
modeldatabase.tempdbdoesn't really map to a schema; its purpose is handled by system temporary tablespace(s) likeTEMPSPACE1.Therefore, technically the closest equivalent to MS SQL's
USEstatement isSET SCHEMA. Issuing theSET SCHEMA xstatement tells DB2 where to look for unqualified objects.However, it's not a requirement to issue the
SET SCHEMAstatement if you fully qualify your object names. The following statements:are equivalent to:
Using
set schemasimply allows you to use unqualified objects.