Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2012
    Posts
    30

    Unanswered: DB2 query optimization

    Hi All!

    I'm using DB2 v9.7.5 for quering data from tables, view. Transactions have only 5-10% from all operation with DB.

    When DB2 server starts queries return data during 1-2 seconds. But then after 100-200-500 queries query time decrease to 1-5 millisec. If there's no queries DB2 suspends (query time becomes 1-2 second again), and wait for a big amout again.

    If Run Configuration Manager (through DB2 Administration Tool) DB2 speeds up again. Here's SQL commands
    Code:
    UPDATE DATABASE CONFIGURATION FOR DLIB USING APPLHEAPSZ 256 AUTOMATIC;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING CATALOGCACHE_SZ 466 ;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING CHNGPGS_THRESH 80 ;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING DBHEAP 1324 AUTOMATIC;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING DFT_DEGREE ANY ;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING DFT_EXTENT_SZ 32 ;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING DFT_PREFETCH_SZ  AUTOMATIC;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING LOCKLIST 4096 AUTOMATIC;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING LOGBUFSZ 256 ;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING LOGFILSIZ 4096 ;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING LOGSECOND 0 ;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING MAXAPPLS 40 AUTOMATIC MAXLOCKS 22 AUTOMATIC;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING MINCOMMIT 1 ;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING NUM_IOCLEANERS 2 AUTOMATIC;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING NUM_IOSERVERS 9 AUTOMATIC;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING PCKCACHESZ 1788 AUTOMATIC;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING SOFTMAX 1100 ;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING LOGPRIMARY 22 ;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING SORTHEAP 891 AUTOMATIC;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING STMTHEAP 8192 AUTOMATIC;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING STAT_HEAP_SZ 4384 AUTOMATIC;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING UTIL_HEAP_SZ 34399 ;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING SELF_TUNING_MEM ON ;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING AUTO_MAINT ON ;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING AUTO_DB_BACKUP OFF ;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING AUTO_TBL_MAINT ON ;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING AUTO_RUNSTATS ON ;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING AUTO_STATS_PROF OFF ;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING AUTO_REORG OFF ;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING AUTO_PROF_UPD OFF ;
    UPDATE DATABASE CONFIGURATION FOR DLIB USING SHEAPTHRES_SHR 29429 AUTOMATIC;
    UPDATE DATABASE MANAGER CONFIGURATION USING AGENT_STACK_SZ 16 ;
    UPDATE DATABASE MANAGER CONFIGURATION USING ASLHEAPSZ 15 ;
    UPDATE DATABASE MANAGER CONFIGURATION USING FCM_NUM_BUFFERS 1024 AUTOMATIC;
    UPDATE DATABASE MANAGER CONFIGURATION USING INTRA_PARALLEL OFF ;
    UPDATE DATABASE MANAGER CONFIGURATION USING MAX_QUERYDEGREE 1 ;
    UPDATE DATABASE MANAGER CONFIGURATION USING NUM_POOLAGENTS 100 AUTOMATIC;
    UPDATE DATABASE MANAGER CONFIGURATION USING NUM_INITAGENTS 0 ;
    UPDATE DATABASE MANAGER CONFIGURATION USING RQRIOBLK 32767 ;
    UPDATE DATABASE MANAGER CONFIGURATION USING SHEAPTHRES 0 ;
    CONNECT TO DLIB;
    ALTER BUFFERPOOL BP32  SIZE 6424 ;
    ALTER BUFFERPOOL IBMDEFAULTBP  SIZE 25903 ;
    SET CURRENT QUERY OPTIMIZATION = 7;
    COMMIT;
    CONNECT RESET;

    Is DB2 optimized for big (maybe huge) amount of queries? How to configure for small or medium queries?

    Thanks.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    When DB2 server starts queries return data during 1-2 seconds. But then after 100-200-500 queries query time decrease to 1-5 millisec. If there's no queries DB2 suspends (query time becomes 1-2 second again), and wait for a big amout again.
    the behaviour is as expected... afteryou start the db, for the first queries, data has to be fetched from disk and populated in bufferpool(memory) .. after a few 100 queries, the data is already in the bufferpool and hence the performance improvement.

    to avoid db2 "suspending" itself when no queries are running, issue a activate database command .. issue this every time you start the db2 instance. this command ensures the memory and other resources are not deallocated when there are no connections to the database. To deallocate the resource, you have to do deactivate database command.

    ACTIVATE DATABASE X
    DEACTIVATE DATABASE X


    --
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Oct 2012
    Posts
    30

    Question

    Quote Originally Posted by sathyaram_s View Post

    ACTIVATE DATABASE X
    DEACTIVATE DATABASE X


    --

    Can it be configured automatic, while db start to sleep?

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    if db deactivated, it will only start to sleep when the last connection is released..
    and activated when first connection comes in
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Oct 2012
    Posts
    30
    How configure DB2 idle time? Or it's system defined value and cannot be modified?

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    what is meant with idle time
    do you mean idle agent ?
    some reading might help
    idle_agents - Number of Idle Agents monitor element - IBM DB2 9.7 for Linux, UNIX, and Windows
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  7. #7
    Join Date
    Oct 2012
    Posts
    30
    Quote Originally Posted by przytula_guy View Post
    what is meant with idle time
    do you mean idle agent ?
    Minimum time when there're no requests to db, and it deactivates..

  8. #8
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    it deactivates after last connection has been released (if db is not activated)
    see usage notes at
    http://publib.boulder.ibm.com/infoce.../r0002039.html
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  9. #9
    Join Date
    Oct 2012
    Posts
    30
    Ok. Thanks.

    As far as i understand.
    1. DB2 starts - it's is deactivated - waiting for income requests
    2. Request comes, DB activates. But bufferpool is not filled, so query time is long
    3. Then coming requests (multi), pool filled - so query time become smaller
    4. All Requests released, clear buffer, DB2 deactivates
    5. New request - time will become smaller after buffer filled

    So, to achieve small query time - nee to decrease buffer size (to fill its size faster).

  10. #10
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    quote : So, to achieve small query time - nee to decrease buffer size (to fill its size faster).
    Reply With Quote ---> wrong
    bufferpool can be set to automatic size
    the larger the bufferpool - the larger the chance a page is available and no physical io is needed
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  11. #11
    Join Date
    Oct 2012
    Posts
    30
    What happens with new requests if buffer is filled?

  12. #12
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    please read some doc about db2 and try to find out
    db2 will try to find victim pages (depending on ...) to flush or to write to disk and flush..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  13. #13
    Join Date
    Apr 2013
    Posts
    19
    which admin tool u used to run configuration manager

  14. #14
    Join Date
    Apr 2013
    Posts
    1

    DB2 query optimization

    Quote Originally Posted by Black Moon View Post
    How configure DB2 idle time? Or it's system defined value and cannot be modified?
    i suspect you mean "idle time until db is deactivated".
    maybe you should first of all understand how db2 is working ...

    1) instance is being started (by default no database "inside" this instance is activated),
    2) you connect to one of the databases residing under this instance,
    3) this database is getting activated implicitely (memory segements are being alocated - not populated!),
    4) your first queries are populating the bufferpools, subsequent queries are improving,
    5) as your last connection terminates, database is getting deactivated, memory is getting dealocated (no idle time configurable),
    6) when you connect again, it restarts at point 2).

    so, if the performance is relevant from the first second, you have to "preactivate" this database explicitely.
    then the memory will not be dealocated after the last connection terminates and your performance will be more constant.

    maybe this helps a bit understanding the "mysteries" of DB2.

Posting Permissions

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