MVSFORUMS.com

[pdhanvanthar]

hi ,

I have a strange problem may be anyone of you have any idea. My transaction uses stored procedures to build cursors dynamically . This is done to improve the performance of the cics trans. Now what happenes is when I hit the transaction first time it takes much time , but repeated searches takes less time . Any idea of why more time is taken when I use this first time ?

[sriramla]

Dynamic SQL's are usually cached by the database to optimize the performance. When the same query is executed many times the access path that is already determined wil be reused. Note that DB2 just needs the query format (columns in the SELECT clause, conditions in the WHERE Clause etc) to build the path. The exact data is not a major influence there (unless my understanding is incorrect).

When there are too many different dynamic queries executed then the oldest one will be gone from the cache. This means next time when that query is needed, DB2 will start from the scratch to determine the access path.

Hope this explains the situation you have described here.

[jsharon1248]

One more possibility is higher buffer hit ratios. DB2 loads data into the buffers for the first query, and then subsequent queries find the data in the buffers eliminating the need to do the physical reads.

[A_programmer]

There is a concept of stay resident for stored procedure. If you provide this option during the Create of the stored procedure, stored procedure always remains in the memory.
_________________
Its not necessary to change. Survival is not mandatory.

[Terry_Heinze]

Are you certain the stored procedure is what's taking the extra time the 1st time through? I used to experience similar behavior with CICS transactions without any data base involved. The 1st time after a CICS refresh, the program needed to be fetched from the load library. After that, it didn't need fetching. Granted this was many releases ago of CICS, but it's a possibility.
_________________
....Terry