Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2007
    Posts
    72

    Unanswered: Looking for GET_DB_CFG function

    Hi all,
    I am looking to find the GET_DB_CFG function. We are DB2v8.2 fixpack 14 and I looked in the user defined function folder of

    db2 control center but it is not there. Should it be somewhere else?

    I'll appreciate your help in locating this very important funcion.

    Thanks

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    This is a stored procedure and not a function as found in some documentation.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Mar 2007
    Posts
    72
    Hi sathyaram,
    You are quite right. It is a store procedure. How then can I use it in a script?

    Take this script for example:
    --call the stored procedure to load up the db config parms into a global temp table called
    -- session.db_config
    call sysproc.get_db_config();
    -- this query shows the percentage of lock list currently being used, and the percentage
    -- of maxlocks that this represents.
    -- if you are over 100% of maxlocks then any one application may be approaching the maxlocks
    -- limit (depending on which applications are the holders of the most locks.
    with dbcfg as
    ( select 1 as row,
    float(locklist*4096) as locklist,
    float(maxlocks) as maxlocks
    from session.db_config
    where dbconfig_type = 1
    ),
    dbsnap as
    ( select 1 as row,
    lock_list_in_use,
    appls_cur_cons as NumCons
    from db2$mon_db
    )
    select dec((lock_list_in_use/locklist)*100,4,1) as "% Lock List",
    dec((lock_list_in_use/(locklist*(maxlocks/100))*100),4,1) as "% to Maxlock",
    NumCons as "Number of Cons",
    lock_list_in_use/numcons as "Avg Lock Mem Per Con (bytes)"
    from dbcfg c, dbsnap s
    where c.row = s.row
    ;

    When I try to run this in my linux environment, I am getting the following error
    SQL0204N "SESSION.DB_CONFIG" is an undefined name. SQLSTATE=42704.

    Have any idea what is wrong here?

    Thanks

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Not sure what your problem is ... A cutdown version of the sql works for me :

    Code:
    --call the stored procedure to load up the db config parms into a global temp table called
    -- session.db_config
    call sysproc.get_db_config();
    -- this query shows the percentage of lock list currently being used, and the percentage
    -- of maxlocks that this represents.
    -- if you are over 100% of maxlocks then any one application may be approaching the maxlocks
    -- limit (depending on which applications are the holders of the most locks.
    with dbcfg as
    ( select 1 as row,
    float(locklist*4096) as locklist,
    float(maxlocks) as maxlocks
    from session.db_config
    where dbconfig_type = 1
    )
    select * from dbcfg
    ;
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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