I have some questions about performance.I developed a Cobol program to fetch e return 100 rows, per Call, from a DB2 Table.
In this program there are four simple SQL commands:
DECLARE CURSOR with a simple clause :
SELECT COD, DATE_CTR FROM TAB WHERE COD >= :cod ORDER BY COD
So, my table have arround 50.000 rows, and each execution of this program returns a array with 100 occurrences (100 rows), deppending on WHERE Clause (host variable cod). Can I reach best performance using DB2 options in DECLARE sentence? Or the best way is using a simple DECLARE and FETCH (until 100 rows)?
I'm talking about options: "Fetch First n rows and Optimize.
Could you me?
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
Fetch first 100 rows doesnt mean the cursor will fetch 100 rows at a time ... if 150 rows satisfies your where clause the result set when you open the cursor will have only first 100 rows .... 50 rows will be discarded .... this clause wont help you in performance improvement ....
If you need to return your 50k rows to the calling program y not open the cursor and return the control back to the calling program ..let it fetch data from the cursor ... you need to declare your cursor with return ...this will reduce traffic also as you dont have to return an array ...