anyone know a way via SQL to get a a list of all databases on a db2 instance?

I didn't find any syscat views that seem to have it: http://publib.boulder.ibm.com/infoce.../r0008443.html

There is a DB2 command (not a SQL statement): LIST DATABASE DIRECTORY

The thing is that databases are independent from each other. Each database (in DB2 LUW) has its own catalog and database A doesn't know anything about database B. As you already mentioned in your question: databases belong to an instance, so you have to ask the instance about its databases.

thanks for that Knut - do you know if there is any API or otherwise programmatic way to get the output of LIST DATABASE DIRECTORY?

I am trying to get this information from within a program.

Yes, there is an API: db2DbDirGetNextEntry API - Get the next system or local database directory entry - IBM DB2 9.7 for Linux, UNIX, and Windows

One thing to note about the "LIST DATABASE DIRECTORY' command. It will return all databases "cataloged" by the instance even if they are remote databases belonging to another instance or server.

Andy

for better or worse the app I am building is .NET running on windows - is there a managed version of this API?

I would say:

open a db2cmd

set db2instance="Instance name"

db2 list db directory

if platform is aix u can use :
db2 list db directory | grep -ip indirect | grep -i alias | awk '{print $4}'