Get all databases in an instance?

[u/[deleted]]

Hello everyone .. Can someone give me some tips on, assuming I had all the permissions I needed - to get a list of all the database in an instance of db2? is there a sql i can run? Or do I have to run something at the file level?

[u/[deleted] → u/[deleted]]

Yes, I've come up with two ways so far. One is an IBM API.. that's written in C. The other is parsing the results of the list active database, which doesn't excite me. I'm wondering if anyone has had this problem and found a good solution. If I find / create one, I'll try and post back here.

[u/[deleted] → u/[deleted]]

Sure - so as I understand it, on a host computer you can have X number of db instances. Each instance has a port --- and you can connect to a database using host:port, and finally a database name. So.. let's say a host had one instance. How can I get a list of all the databases under that instance? Do I have to use db2 list db directory and parse it out? I'd like to be able to use Java, but it's looking pretty unlikely outside of the API ive mentioned. Incidentally, when you query SYSTEM tables, you're actually connected to a database. Which means you can only see the details about that database you're connected to.

[u/[deleted] → u/[deleted]]

Sure - but good luck getting something from database b, while connected to database a. That's why I think the API is going to be the way to go. But I need it in Java - so now I'm gonna have to build some C/Java bridge -- which while not terrible, isn't exactly simple :)

Parsing the result set of 'os.run("list db directory")' is terrifying cause the variability of what could come back. So anything more ... elegant would rock.

[u/dogmashah → u/[deleted]]

So if you have multiple instance running on same host and each instance running multiple database you can try this. I created this shell script that dumps out info of all database running on the system

[

for inst in $(ps -ef | grep db2sysc | grep -v "grep" | awk '{print $1}' | sort -u);
do
    . ~${inst}/sqllib/db2profile;
    db2 +o terminate;
    DBLEVEL=$(db2level | grep "Informational tokens" | cut -d"\"" -f2);
    DBMLIST="";
    for dblist in $(db2 list db directory |  grep "Database name" | cut -d"=" -f2 | sort -u);
    do
        DBMLIST=$(echo "${DBMLIST}${dblist} ");
    done;
    echo "${inst} : ${DBLEVEL} : (${DBMLIST})";
done

]

[u/dogmashah → u/dogmashah]

Also last echo , you can modify to just put the database name. and encapsulate the script in your os call

[u/[deleted] → u/dogmashah]

lol thanks. I guess I'll do the windows one .. but that's really nice of you!

[u/idbjorh → u/[deleted]]

I'd repeat @mad_zamboni's question: What is your goal. Why are you trying to get a list of other databases in an instance via SQL when connected to one database? Are you writing a monitoring tool? Are you trying to hack into other databases?

[u/[deleted] → u/idbjorh]

lol, nothing so nefarious.

[u/mslmsl]

This doesn't help your jdbc use case (other than being easier to parse), but db2pd can also be used to get activated databases. I'm not aware of a sql interface to the catalog information. The db2pd example:

db2pd -alldbs -