Hi All, I have 2 doubts regarding DB2
1 ) Does dynamic SQL snapshot contains only dynamic SQL or both static and dynamic .
If only dynamic, when I flush the dynamic package cache why it only deletes some of the data and not all. Also , I can see some of the static SQLs executed by me in the dynamic SQL snapshot.

2) while going through asynchronous and synchronous reads, I came across two different formulas to calculate it

for synchronous read ratio :
SRP = 100 - (((Asynchronous pool data page reads + Asynchronous pool index page reads) x 100) / (Buffer pool data physical reads + Buffer pool index physical reads))

and for asynchronous read ratio :
ARP = ((Asynchronous pool data page reads + Asynchronous pool index page reads) / (Buffer pool data logical reads + Buffer pool index logical reads)) * 100%


My confusion is why while taking synchronous read ratio we are comapring it with physical reads only and while calculating asynchronous read ratio we are comparing it with logical reads. Shouldn't ARR be compared with physical reads only and if we are calculating the number of Asynchronous pages read, shouldn't we subtract unread prefetch pages from the numerator of ARR formula.

Is I am missing something somewhere. Can anyone explain it to me.I have searched the whole internet but never got any satisfactory reasons.

Thanks for help

1) Only dynamic. The reason you still see statements after a FLUSH is that a flush only marks the cache as invalid, it does not erase the sql. This will happen as subsequent sql is processed.

2) Why would you want to subtract an asynchronous read that did not get used? You still had to incur the cost of the read so it should count.

Andy

Hi Andy,
thanks for replying.
1) why can i see the statements I have executed locally like some select count(*) from tables in dynamic SQL snapshot
2) why Synchronous read is divided by physical reads and asynchronous reads by logical reads, why asynchronous reads is not divided by physical reads while calcualting ARR

1) Because that is still dynamic SQL.
2) Because a synchronous read is when the DBM has to wait for the data to be retrieved from disk (physical read). An asynchronous read is when the physical reads are done ahead of time and the data is read from the bufferpool. Reading from the bufferpool is a logical read.

Andy

Thanks Andy for clearing my doubts, just one more confusion
what if I have a tablespace which has both synchronous read ratio and asynchrnous read ratio very low. Now according to what i have learnt about them is when ARR is low that implies SRR is high and vice versa. so what should I conclude from it.
I think my whole understanding is a bit messed.

You want synchronous ratio low. Synchronous reads are "bad". When the ARR is low using this formula, this is not necessarily bad. It means that you are reading a lot of stuff from the bufferpool and not having to read from disk. This is actually good. I bet your bufferpool hit ratio is high for this tablespace. I am also betting that your DB is OLTP as apposed to DW.

Andy