Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2004
    Posts
    38

    Unanswered: From DB2 Client - More time to fetch result

    I'm facing a performance issue on my project hence it would be great if you could give me some idea.

    Env:
    DB2 UDB V8.1 FP 10, Partitioned database with 8 logical partitions on AIX 5.3
    DB2 Client v8.1 resides on Solaris box

    Problem:
    A particular query takes around 90 seconds to execute against a table(non partitioned) which has nearly 6 million records when executed on the DB2 Server directly (local client) but when the same query was executed from the Solaris Client box (remote client) it executes for a very long time (765 Second s)

    The Query:
    SELECT TREAD_INCDNT_KEY, SEQ_NBR, SR_NBR, SR_OPENED_DT, NHTSA_RPTD_FLAG, UPDT_TIMSTM, VIN FROM TREDIZ1.CARS_SIEBEL_SR Order By TREAD_INCDNT_KEY, SEQ_NBR Asc

    I have a indexes on TREAD_INCDNT_KEY and SEQ_NBR. Table & Index statistics are looking fine. Explain plan is same whether the query is executed either from server or from client. I feel that this is due to network issue hence asked the help of network admins. But it would be helpful if you feel that there might be a issue from database perspective.

  2. #2
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    Did you check the speed of the Network?
    As you are saying at the local end the query returns result immediately, but from the remote-client it is slow. I donot feel that its an issue at the DB2 end.

  3. #3
    Join Date
    Jun 2004
    Posts
    38
    Network speed is not an issue I feel as I'm able to FTP a 1GB file in 49 seconds between these boxes.

    Yes you are correct in your other point. From local client the query is fast while from remote client its very slow. The result set is expected to be around 6 Million rows.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Application Snapshot elements to check:

    Accept</SPAN>ed</SPAN> Block Cursor Requests
    Open Remote Cursors with Blocking
    Rejected Block Cursor Requests

    Config parms to check:

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

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    And, What is the BLOCKING of your package ?

    try making it BLOCKING ALL ..

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

  6. #6
    Join Date
    Jun 2004
    Posts
    38
    How can I check the blocking level of my package? from the DB2 Client we are using Ascential Datastage.
    If I test it by having just the select statement how should I specify the blocking level? I read information centre to get details about it but thing are still vague to me.

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    IIRC, BLOCKING LEVEL is always at the package level ...
    If you are running from command line, then make sure you bind db2ubind package list with blocking all

    db2 bind @db2ubind.lst blocking all grant public

    the lst file is in sqllib/bnd ...

    Yes, the manual is very vague ... Start with you packages and make sure you monitor the application block requests to see if it fails ... then try tuning the configs ..

    HTH

    Sathyaram
    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
  •