Unanswered: Getting Database list for each instance
I have installed 2 instances of DB2 (version 7.2) on AIX 4.3.3.
I have tried 3 different methods to get a list of databases that resides in a given instance. The first 2 methods only works for the first instance but not the second one. The 3rd method failed for both instances.
In a for loop with 2 instances,
1. Use putenv and set DB2INSTANCE to the instance value,
i.e 1st iteration db2inst1, then db2inst2.
2. Call sqleatin.
3. Call sqledosd.
3.1. Call sqledgne and prints db name on this instance.
4. Call sqledcls
5. Call sqledtin
6. Use putenv and reset DB2INSTANCE to empty.
When the program enters the 2nd iteration, the resulting list returned is still the same as that from the 1st instance. If I reversed the order of instances i.e. db2inst2 then db2inst1, for 2nd iteration I still get list of dbs on db2inst2 instead of list of dbs on db2inst1. What's wrong here?
Same steps as 1st method except use SQLDataSources instead of sqledosd.
Results are the same as those from 1st method.
Use db2GetSnapshot instead of SQLDataSources.
Result: Even worse, I got SQL1611W "No data was returned by Database System Monitor". This errmsg indicate I need to activate a db 1st. However can I activate a DB if I canot get a list of DBs in the 1st place?
I've been struggling with this for weeks now with no success.
This is no surprise....
I had the very same experience when I started out with DB2 a year ago.
Are you checking the SQLCODE from the "sqleatin" call? I am sure that it should have returned a non-zero SQLCODE for the second instance.
This is because, it is impossible to attach to more than one instance from a SINGLE application (unless you catalog the second instance into the node directory of the first instance). Eevn after you have done this and successfully attached to the first instance, you will not get the dabase names from the second instance. This is because "sqledgne" prints database names from the instance pointed by the DB2INSTANCE variable only (this api does not require an instance attachment).
When your program issues the first api, DB2 does the initialization and finalises all environment variables. After this point, you cannot change any of the environment variables (db2 ignores the change). Since DB2INSTANCE always points to the "first instance", you always get database names from the "first instance".
The only work around for this problem is to use multiple contexts.
By default, DB2 uses a single context per application. To manage multiple contexts, use the following APIs:
sqleAttachToCtx - Attach to Context
sqleBeginCtx - Create and Attach to an Application Context
sqleDetachFromCtx - Detach From Context
sqleEndCtx - Detach and Destroy Application Context
sqleGetCurrentCtx - Get Current Context
sqleInterruptCtx - Interrupt Context
sqleSetTypeCtx - Set Application Context Type
Note: db2GetSnapshot API will never return list of database names in an instance. You can use this API to get a list of applications currenly connected to a given instance/database.