Hi ,

I have created a report dashboard in the BPM .To get the data in the reports Iam using custom DB2.Here iam facing an issue with the query execution time as it is taking more than three minutes as there is a joins in the tables.I have created indexes also as per suggestion .Both DB2 and BPM are there in the same server.When query is taking long time it is getting impact on the BPM also.

Request for any suggestion.

Hi Chakra,

Its not best practise to keep BPM and DB2 in same server.  Did you check how much its taking when ur exectue the query in the sql client outside of the BPM(Db2 studio , toad etc)?

Also you may want to check if the resources proviosoned enough in the systee,(CPU, RAM ).

Not sure the details of your query and why it is taking so long, but I had an issue with DB2 one time where there were a lot of queries being executed against the database simultaneously and changing our queries to use uncommitted reads helped performance dramatically.  In DB2, all we had to do was add "WITH UR" to the end of our query, so something like:

SELECT *
FROM TABLE1
JOIN TABLE2 on TABLE2.ID = TABLE1.ID
JOIN TABLE3 on TABLE3.ID = TABLE2.ID
WHERE COL1 = ?
AND COL2 = ?
WITH UR

You might try that out and see if it helps.  Perhaps also provide your query here, if possible, as there may be another way to write it that performs better.