I have a very strange db2 performance problem. I have a sql which join 5 tables and also have a predicate <>. By using cli, it runs about 7 hours to get 8736409 rows result.
By using db2batch , it only takes about 55 minutes to get the same results. (I did restarted db2 instance to avoid cache, also, file system cache is not enabled. ) Now the difference between db2batch and cli is db2batch uses RR, but my cli is CS, so I used db2 change isolation to RR in command line and re-run the sql in cli, I got similar speed as db2batch. I repeated for many times and got same result.
I don't know why RR can make this big change. Anything wrong with my database?
My environment: DB2 8.1 fixpack 14. 64 Bits. Single partition data mart. intra-parallel : YES.
By using db2batch , it only takes about 55 minutes to get the same results. (I did restarted db2 instance to avoid cache, also, file system cache is not enabled. ) Now the difference between db2batch and cli is db2batch uses RR, but my cli is CS, so I used db2 change isolation to RR in command line and re-run the sql in cli, I got similar speed as db2batch. I repeated for many times and got same result.
I don't know why RR can make this big change. Anything wrong with my database?
My environment: DB2 8.1 fixpack 14. 64 Bits. Single partition data mart. intra-parallel : YES.
Comment