Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2002

    Unanswered: Getting Database list for each instance

    Hello all,

    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.

    1st method:
    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?

    2nd method:
    Same steps as 1st method except use SQLDataSources instead of sqledosd.

    Results are the same as those from 1st method.

    3rd 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.


  2. #2
    Join Date
    Apr 2002
    Hello Sam,

    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.

    Last edited by dbfocris; 04-30-02 at 17:43.

  3. #3
    Join Date
    Aug 2011

    I am also facing the same problem, tried with CTX API still not getting the Db names

    Can you please, explain more how can i use CTX API ,
    do you have any sample for this?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts