Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746

    Unanswered: starting to doubt "connection concentrator"

    The concept behind the "connection concentrator" always appealed to me and I implemented this at 1 of my clients ( a db2 content manager site) and I am considering this at my other client (a typical db2/was site). Recently I was involved in a process of processing a large file to be parsed and loaded into db2. So I started to script in ksh/awk and the result was a large file containing thousands of generated SQL's and the last line of my script was to invoke "db2batch" and I instructed to it to execute all the SQL's in that file (I generated merge and insert statements, each processing multiple rows).

    I am a man so I like to watch
    During the execution of the db2batch process I monitored the database with both "db2top" and "db2mon"....
    Did not like what I saw: most of the time the session was " Decoupled from coordinator"..... what the *beep*? Why? There is plenty of work to be done!
    It seems that the "connection concentrator" ALWAYS releases the agent after the SQL work is done and when the next SQL comes up another agent is assigned from the pool.... works as designed.. okay... but how about my batch-load of work where more than a thousand queries which must be executed as fast as possible? It seems that there is a lot of overhead involved in dropping/assigning agents between 2 SQL's and the is not way to overrule way to connect, is there?

    It makes you think: how does this affect your other connections? When you have a WAS client with connection pooling, how does WAS divide the workload over those connections? 1 java process will fire multiple SQL's to process 1 transaction and does that mean that db2 is constantly busy with dropping/assigning agents for that transaction or will it stay active untill a "commit" or "rollback" is executed?
    But then again: how can you tell? It is hard to distinguish 1 WAS connection from another and how can you measure the overhead involved by the agent-coordinator?

    So I will test my db2batch work again on a test-database with "connection concentrator" disabled and I am very curious about your experiances with the connection concentrator.
    Is it as good as it seems or <sigh> "it depends"? I suspect the latter but then you must know on what it depends and how to measure.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Disclaimer:I have not used connection concentrator and have little knowledge on this subject

    but how about my batch-load of work where more than a thousand queries which must be executed as fast as possible?
    I do not think connection concentrator was designed for an environment like this.
    It is for an environment with large number of connections - with each connection being active for a short interval followed by long periods of inactivity.

    Of course, it will be nice if an agent is 'coupled' to a connection for an extended period after the transaction is completed - with a parameter to define this period.

    1 java process will fire multiple SQL's to process 1 transaction and does that mean that db2 is constantly busy with dropping/assigning agents for that transaction or will it stay active untill a "commit" or "rollback" is executed?
    According to this article, the agent is coupled to a connection till commit/rollback.

    Enhance performance using connection concentrator in DB2 Universal Database, Version 8

    Google search lists many other web pages which describe tuning the agent parameters to manage connection concentrator


    BTW,did you manage to get any stats on the overhead?
    Last edited by sathyaram_s; 03-22-11 at 20:08.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by sathyaram_s View Post
    BTW,did you manage to get any stats on the overhead?
    Not yet. The "test" database is shared by a lot of other people so I cannot bring it "up & down" when I feel like it. Soon I will have a sandbox for me to play with and then I will do some tests to compare the effects. I'll keep you all informed.

  4. #4
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    as promised:

    with concentrator:
    real 5m9.49s
    user 0m34.89s
    sys 0m20.96s

    without :
    real 4m46.51s
    user 0m34.74s
    sys 0m21.00s

    From now on I am more reluctant to implement this feature.....

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Thanks for sharing.

    Is this for the batch process you mentioned above ?

    And where did you get the informaton from ? Unix?

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

  6. #6
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Yes. On the "sandbox" machine: aix 5.3 /Informational tokens are "DB2 v8.1.1.112", "s060429", "U807381", and FixPak "12

    I've got a file containing multiple SQL statements and I use "db2batch" to process. Like this:
    Code:
     
    time db2batch -d xxxx -f /tmp/xxx.sql  -r /tmp/xxx.log  -o p 2 o 5 e 0  -i complete
    IBM confirms my thoughts: https://www-304.ibm.com/support/docv...id=swg21319866

    So on my 32bit aix clients site I will continue to use it because I need every Kb I can spare. My other client has V9.7 on a 64 bit machine with plenty of RAM and there I will withdraw my recommendation to implement this feature.

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Thanks for the info.
    I am surprised that the CPU usage in your example is the nearly the same for the case with and without the concentrator.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    No, do not draw that conclusion. It is the CPU usage of the "db2batch" program, not of DB2 itself. The "real" time is the time spent and because I was the only user on this system it is significant.

Posting Permissions

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