Db2 – Get all the database in db2 through web

cdb2oledb

I would like to get all the databases available on a db2 instance from a C# application.
The CLP command to be used is LIST DB DIRECTORY.

How can I fire this command from C#? Is there another select statement I can use to get all the databases on one db2 instance?

Best Answer

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).

Related Question