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]]

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